MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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?

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


My post above.

Posted by Damon Ostrander on August 09, 2001 8:38 PM

Here's a fraction function that provides more control


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:


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

' Format the result into a string
If Numerator <> 0 Then
FormatFraction = CStr(WholePart) & " " & CStr(Numerator) & "/" & CStr(Denominator)
FormatFraction = CStr(WholePart)
End If

End Function

One caveat: the value it yields is not a number, but rather a text string.

Happy computing.