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?

OOPS! 5/32 not 5/16.

"jct6641" wrote:

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?

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).

--
Rick (MVP - Excel)


"jct6641" wrote in message
...
OOPS! 5/32 not 5/16.

"jct6641" wrote:

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?

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"jct6641" wrote in message
...
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?

On Thu, 2 Jul 2009 11:15:01 -0700, jct6641
wrote:

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

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.

"Bernard Liengme" wrote:

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"jct6641" wrote in message
...
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?

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

"Rick Rothstein" wrote:

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).

--
Rick (MVP - Excel)


"jct6641" wrote in message
...
OOPS! 5/32 not 5/16.

"jct6641" wrote:

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?

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

"Ron Rosenfeld" wrote:

On Thu, 2 Jul 2009 11:15:01 -0700, jct6641
wrote:

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

Hi,

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

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"jct6641" wrote:

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

"Rick Rothstein" wrote:

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).

--
Rick (MVP - Excel)


"jct6641" wrote in message
...
OOPS! 5/32 not 5/16.

"jct6641" wrote:

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?

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.

--
Rick (MVP - Excel)


"jct6641" wrote in message
...
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.

"Bernard Liengme" wrote:

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"jct6641" wrote in message
...
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?

On Thu, 2 Jul 2009 13:08:01 -0700, jct6641
wrote:

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

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

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

"Bernard Liengme" wrote:

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"jct6641" wrote in message
...
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?

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

"DWR" wrote in message ...
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.

On Mon, 17 Aug 2009 22:34:01 -0700, DWR wrote:

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