What's the cell format of cell O14? What's the

formula used in cell O14?

Why are your formatting as...

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

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.

Thanks Mark Re: Why are your formatting as...

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