# 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

# ??/??

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

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 # ??/??

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 &LT;&GT; Numerator \ 2 Or Numerator &LT; 2&LT;br> Denominator = Denominator / 2
Numerator = Numerator / 2
Loop

' Format the result into a string
If Numerator &LT;&GT; 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