Fractions

[jct6641]

When converting a decimal to a fraction, I get some responses like 1/67 when
what I am looking for is increments of 1/64. Then I want to display 10/64 as
5/16 or 32/64 as 1/2 etc.
Can this be accomplished in an excel formula or combination of nested
formulas?

 

[jct6641]

OOPS! 5/32 not 5/16.

 

[Rick Rothstein]

Not sure if this is the best way to do it or not (so check back here to see
what other responders post), but this seems to work...

=TEXT(A1,"#/"&64/GCD(SUBSTITUTE(TEXT(A1,"#/64"),"/64",""),64))

Note: The GCD function requires the Analysis ToolPak add-in to be active
(automatic in XL2007, use Tools/Add-Ins to activate it in lesser versions of
Excel).

 

[Bernard Liengme]

If you could be happy with the data as displayed below, then use custom
format # ??/16
0.76 4.54 8.53
12/16 4 9/16 8 8/16

But you want data like
0.76 4.54 8.53
3/4 4 9/16 8 1/2

So you first need to round to the the nearest 16 then use the fraction
format # ??/??

I have used the formula
=ROUND(A1*16,0)*(1/16)
to do the rounding in the second row

best wishes

 

[Ron Rosenfeld]

When converting a decimal to a fraction, I get some responses like 1/67 when
what I am looking for is increments of 1/64. Then I want to display 10/64 as
5/16 or 32/64 as 1/2 etc.
Can this be accomplished in an excel formula or combination of nested
formulas?

Round the result to the nearest 64th;
Format the cell as a fraction with up to two digits.

And yes, it will display just a single digit when appropriate.

To Round the result:

=ROUND(A1*64,0)/64

Or =ROUND((Your_Formula)*64,0)/64

Or =mround(a1,1/64)



--ron

 

[jct6641]

Drawing Inches to Feet & Inch Conversion
Inches from Print 0.1880 Feet Whole Inches Fractions Low Tolerance High
Tolerance
905.188 = 75 5 3/16 5/32 7/32

This is the result I got & it is EXACTLY what I was looking for. Thanks a
million.

 

[jct6641]

I do not have access to GCD, so I could not try it. Appreciate the response
anyway.

 

[jct6641]

I already tried this, but thanks for the response anyway.

 

[Shane Devenshire]

Hi,

The GCD function is part of the Analysis ToolPak, choose Tools, Add-ins and
put a check beside the ATP.

 

[Rick Rothstein]

Just so you are aware, when using a Cell Format of Fraction - Up To Two
Digits, Bernard's formula gives 1/8 for a value of 10/67 whereas Ron's
formula gives 5/32 which is what you said you wanted.

 

[Ron Rosenfeld]

I already tried this, but thanks for the response anyway.

What was the problem? You wrote that you wanted to do increments of 1/64th. If
you want a different increment, merely change the factor.
--ron

 

[Bernd P]

Hello,

I suggest to be *very* cautious with the fraction number format
# ??/?? (or similar):
http://sulprobil.com/html/nearest_rational_number.html

=TRUNC(ROUND(A2*64,0)/64,0)&" "&ABS(ROUND(MOD(ROUND(A2*64,0)/64,
(A2=0)+SIGN(A2))*64,0))&"/64"

Regards,
Bernd

 

[DWR]

Hi Bernard,

I am trying to conver the following into an excel sheet from the US Bonds
Market.

This is a tricky one.....today's closing price on Bonds was 119'215

That translates to 119 and 21.5 32nds of point.

In other words 119 21.5/32

How can I get excel to recognize this in this format?


Thanks!

David

 

[Bernd P]

Hello David,

I use
=INT(A1)&REPT("-"&ROUND(MOD(A1,1)*32,2),SIGN(MOD(A1,1)))
to represent
105.703125 as 105-22.5 (105 and 22 1/2 thirtytwo's)
for example.

107.679688 would be 107-21.75 (107 and 21 3/4 thirtytwo's)

Would this help you?

Regards,
Bernd

 

[Hans Terkelsen]

Hi Bernard,

I am trying to conver the following into an excel sheet from the US Bonds
Market.

This is a tricky one.....today's closing price on Bonds was 119'215

That translates to 119 and 21.5 32nds of point.

In other words 119 21.5/32

How can I get excel to recognize this in this format?


Thanks!

David

....

Hi David.

In Bernard's absence try this:
119'215 in A1

=DOLLARDE(SUBSTITUTE(A1,"'","."),32)
That would give the numeric value

Formatted as # ?/64 maybe
the value would be shown as 119 43/64

The poor DOLLARDE is rarely used, so let it get some air

Regards, Hans T.

 

[Ron Rosenfeld]

Hi Bernard,

I am trying to conver the following into an excel sheet from the US Bonds
Market.

This is a tricky one.....today's closing price on Bonds was 119'215

That translates to 119 and 21.5 32nds of point.

In other words 119 21.5/32

How can I get excel to recognize this in this format?


Thanks!

David

You won't be able to express it that way as a numeric value.

You could express it as 119 215/320

First, convert it to a decimal dollar value:

IF you have the Analysis ToolPak installed, or if you have Excel 2007+

=DOLLARDE(SUBSTITUTE(A1,"'","."),32)

If not:

=LEFT(A1,FIND("'",A1)-1)+MOD(SUBSTITUTE(A1,"'","."),1)*100/32


Then, either format the cell as 0 ???/320

or surround the formula with the TEXT function to apply that format.

=TEXT(DOLLARDE(SUBSTITUTE(A1,"'","."),32),"0 ???/320")


--ron