Fractions and superscript help

  • Thread starter Thread starter SOS
  • Start date Start date
S

SOS

I posted a couple of weeks ago but would be grateful for some more hel
in this area.

I have a spreadsheet that adds up points awarded to each person.

It is possible that a person's final total could contain a whole numbe
plus a fraction (ie 20 1/2, 23 1/3, 8 5/6 etc) and what I really wan
to do is to format the fraction part of the cell to be superscript t
make it easier to read.

I have tried various bits of code but all to no avail. I am unable t
use the "characters.count" as you could if the cell contained text a
it returns an error.

I'd be really gerateful if someone could point me in the righ
direction to solve this

Regards

Seamu
 
Hi Seamus,

You could put the integer part in one cell and the fraction in the cell next
to it, left-aligned, with a superscript font.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel
 
Thanks, Niek, for your suggestion. I have begun to (attempt to
incorporate your suggestion into VBA code as seen below but I feel a
if I'm probably going round in circles a bit.

In the example code below the total is in Cell E8 and I've tried t
use an If statement to find out if the value is a fraction and if so t
then RoundDown the value and copy out the fraction part. In order the
to superscript the both the integer and the fraction I have to copy th
cells values and PasteSpecial (valuesonly) them into the next cells.

I just feel that a reasonably simple operation is becoming mre and mor
complicated and any guidance would be appreciated

Thanks

Seamus

Sub Totals()

If Mid(Range("E8"), 2, 1) = "." Then
With Range("F8")
.FormulaR1C1 = "=ROUNDDOWN(RC[-1],0)"
.Copy
Range("G8").PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
End With
With Range("H8")
.FormulaR1C1 = "=Mid(RC[-3],2,2)"
.Copy
Range("I8").PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
Range("I8").Value = "0" & Range("I8").Value
End With
End If

Columns("F:F").Delete Shift:=xlToLeft
Columns("G:G").Delete Shift:=xlToLeft
Range("F8").HorizontalAlignment = xlRight
With Range("G8")
.NumberFormat = "# ?/?"
.Font.Superscript = True
.HorizontalAlignment = xlLeft
End With

End Su
 
I'm not sure if this does what you want, but you may get an idea from it.

I look at the numeric cell and put a string in the cell next to it.

It uses the existing format and looks for a space to separate the whole part
from the fraction. Then it superscripts the fraction and removes the space (in
that adjacent cell).

Option Explicit
Sub Totals2()
Dim spacePos As Long
Dim myStr As String
Dim myCell As Range

For Each myCell In Selection.Cells
With myCell
myStr = .Text
spacePos = InStr(1, myStr, " ")
myStr = Application.Substitute(myStr, " ", "")
With .Offset(0, 1)
.NumberFormat = "@"
.HorizontalAlignment = xlRight
.Value = myStr
If spacePos > 0 Then
With .Characters(Start:=spacePos, Length:=Len(myStr) - 1)
.Font.Superscript = True
End With
End If
End With
End With
Next myCell
End Sub

SOS < said:
Thanks, Niek, for your suggestion. I have begun to (attempt to)
incorporate your suggestion into VBA code as seen below but I feel as
if I'm probably going round in circles a bit.

In the example code below the total is in Cell E8 and I've tried to
use an If statement to find out if the value is a fraction and if so to
then RoundDown the value and copy out the fraction part. In order then
to superscript the both the integer and the fraction I have to copy the
cells values and PasteSpecial (valuesonly) them into the next cells.

I just feel that a reasonably simple operation is becoming mre and more
complicated and any guidance would be appreciated

Thanks

Seamus

Sub Totals()

If Mid(Range("E8"), 2, 1) = "." Then
With Range("F8")
FormulaR1C1 = "=ROUNDDOWN(RC[-1],0)"
Copy
Range("G8").PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
End With
With Range("H8")
FormulaR1C1 = "=Mid(RC[-3],2,2)"
Copy
Range("I8").PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
Range("I8").Value = "0" & Range("I8").Value
End With
End If

Columns("F:F").Delete Shift:=xlToLeft
Columns("G:G").Delete Shift:=xlToLeft
Range("F8").HorizontalAlignment = xlRight
With Range("G8")
NumberFormat = "# ?/?"
Font.Superscript = True
HorizontalAlignment = xlLeft
End With

End Sub
Click to expand...
 
Dave,

Thanks for the suggestion. That has helped a good bit. However havin
followed your example I now find that the formatted cells now can't b
summed as they are text. They also wouldn't be able to be ranked a
the end of the day.

Any further suggestions

Regards

Seamu
 
Seamus:

You still have the original data (as numbers) in your worksheet. Thi
presumably could be used for averages, ranks etc?

Al
 
I like Alf's suggestion (and his old TV show!).

If I wanted to use the original values, I'd keep them. Maybe hide the column so
it's not too cluttered, but I'd keep them.

Where's Lucky?????
 
And to steal from all the suggestions so far --

In column A, store the original values
In column B, a formula to return the integer: =INT(A2)
IN column C, a formula to return the fraction: =MOD(A2,INT(A2))

Hide column A
Format column B as number, with zero decimals, aligned right
Format column C as fraction, and superscript, aligned left

P.S. I'm sure Lucky has use up all his (her?) nine lives by now!
 
Debra,

I really like the sound of your suggestion and have tried it as a
example and it works well. However, if Cell A1 contains only
fraction your suggestion doesn't work and I wonder if there could be a
addition to the formula along the lines of

"If A1 contains only a fraction, return the value of A1 else return th
fraction part of the number in A1"

What do you think

Regards

Seamu
 
Debra,

On futher messing around I have found that

=INT(A1) returns the integer
=MOD(A1) returns the fraction part of A1 regardless of whether there i
an integer or not
=MOD(A1,INT(A1)) returns teh fraction part only if there is an intege
and fraction in A1

Many thanks for your help

Seamu
 
Back
Top