Returning a fraction w/correct format?
Posted by Jim on August 09, 2001 10:45 AM
I have been plagued with this problem for quit a while
in my spredsheet i have approximately 9 cells that perfom
a calculation and puts the result in cell O14,depending
on the data input the resulting number format will change
if for example i have the number cell format set to 8th's
i will get a result of 4/8's instead of 1/2. Does anyone
know how to get around this?
Jim
Posted by Mark W. on August 09, 2001 11:02 AM
What's the cell format of cell O14? What's the
formula used in cell O14?
Posted by Mark W. on August 09, 2001 11:15 AM
Why are your formatting as...
# ??/??
Posted by Jim on August 09, 2001 11:16 AM
Hi Mark,
I've experimented with everything from mround,
custom number formatting etc.Whatever the Number format in O14
is, is the format it returns 1/16's, 1/8's, 1/4's
etc. The formula in O14 is =(F2-M14)the cell format
at the moment is 16th's so it returns 5 8/16's
where as i would like 1/2"
F2= Sqrt(K14^2+C2^2), Ans. = 11 7/8"
C2= (C11/H9)*K14
Thanks for your time,Jim
Posted by Mark W. on August 09, 2001 11:25 AM
Well, yeah...
If you "hardwire" your format as 16th's (i.e, # ??/16)
Excel will always express the results in 16th's. If
you want your fraction reduced then use # ??/??
as your format.
Posted by Jim on August 09, 2001 11:26 AM
Thanks Mark Re: Why are your formatting as...
Posted by Mark W. on August 09, 2001 11:33 AM
See...
My post above.
Posted by Damon Ostrander on August 09, 2001 8:38 PM
Here's a fraction function that provides more control
Jim,
I assume you want a reduced fraction, but that you want to control the denominator so that, for example, it is always multiples of 1/8 (i.e., you never want "3/11"). Here is a simple VBA function that does it. To use it, just enter:
=FormatFraction(A5,8)
to format the value in cell A5 to the nearest 1/8th and reduced. Yes, it does handle numbers larger than 1 and negative numbers.
'______________________________________________
Function FormatFraction(DecimalNumber As Single, Optional SmallestFrac As Integer = 16) As String
' DecimalNumber is the number to be expressed as a whole part and fraction thereof
' Format will display the result in string format, reducing the denominator
' to the smallest possible within the constraints of bringing the number to the nearest
' sixteenth. The optional parameter SmallestFrac allows you to set the largest
' denominator to something other than sixteenths (set to 8 for eights, etc.)
Dim WholePart As Integer
Dim FractPart As Single
Dim Numerator As Integer
Dim Denominator As Integer
WholePart = Fix(DecimalNumber)
FractPart = DecimalNumber - WholePart
Denominator = SmallestFrac
Numerator = Fix(FractPart * SmallestFrac + 0.5) '0.5 Rounds to nearest 16th
' Reduce numerator and denominator by factors of 2
Do Until Numerator / 2 <> Numerator \ 2 Or Numerator < 2<br> Denominator = Denominator / 2
Numerator = Numerator / 2
Loop
' Format the result into a string
If Numerator <> 0 Then
FormatFraction = CStr(WholePart) & " " & CStr(Numerator) & "/" & CStr(Denominator)
Else
FormatFraction = CStr(WholePart)
End If
End Function
One caveat: the value it yields is not a number, but rather a text string.
Happy computing.
Damon
