Fractions to 16ths, 32nds, & 64ths

[A_Pitts]

I'm always getting ideas and help from the board and I wanted to pass along this extention of a formula that I found here.
The original converted a decimal to the nearest 16th AND reduced it to lowest terms.

Referencing the value in cell E2

=IF(MOD(INT(16*(+E2-INT(E2)+0.0312)),16)=0,TEXT(E2,"#"),IF(MOD(INT(16*(+E2-INT(E2)+0.0312)),8)=0,TEXT(E2,"# 0/2"),IF(MOD(INT(16*(+E2-INT(E2)+0.0312)),4)=0,TEXT(E2,"# 0/4"),IF(MOD(INT(16*(+E2-INT(E2)+0.0312)),2)=0,TEXT(E2,"# 0/8"),TEXT(E2,"# 0/16")))))


I added the ability to take it to 32nds here:

=IF(MOD(INT(32*(+E2-INT(E2)+0.015625)),32)=0,TEXT(E2,"#"),IF(MOD(INT(32*(+E2-INT(E2)+0.015625)),16)=0,TEXT(E2,"# 0/2"),IF(MOD(INT(32*(+E2-INT(E2)+0.015625)),8)=0,TEXT(E2,"# 0/4"),IF(MOD(INT(32*(+E2-INT(E2)+0.015625)),4)=0,TEXT(E2,"# 0/8"),IF(MOD(INT(32*(+E2-INT(E2)+0.015625)),2)=0,TEXT(E2,"# 0/16"),TEXT(E2,"# 0/32"))))))

But I am unable to do 64ths in a single cell due to the 256 character limitation for formulas. I did make it work using a separate cell for rounding and then reducing to lowest terms.

=+E2-INT(E42)+0.0078125

and below it

=IF(MOD(INT(64*E3),64)=0,TEXT(E2,"#"),IF(MOD(INT(64*E3),32)=0,TEXT(E2,"#0/2"),IF(MOD(INT(64*E3),16)=0,TEXT(E2,"#0/4"),IF(MOD(INT(64*E3),8)=0,TEXT(E2,"# 0/8"),IF(MOD(INT(64*E3),4)=0,TEXT(E2,"# 0/16"),IF(MOD(INT(64*E3),2)=0,TEXT(E2,"# 0/32"),TEXT(E2,"# 0/64")))))))

If someone has the solution, Thanks. But my main purpose was to make what I have available for everyone. My work partners and I have banished that old conversion reference card to the dustbin of history and improved our speed and accuracy.

Thanks to the entire MrExcel bunch for making this forum available.

 

[nicknicknickandnick]

That's an interesting extension - I'll add that to my notes about fractions and Excel.

I have to point out that the Mr. Excel formula has an error in it, though - all instances of 0.0312 should be replaced by 0.03125, which is the value of half of 1/16. Otherwise, the fraction 1/32, which is half of 1/16, will show as 0 instead of 1/16 (it is customary to round up when a value is halfway). You did not have a typo - it's the fault of the Mr. Excel page on the topic. The page's formula also has unnecessary + symbols in front of E2 in a few places. Oh, I did find a typo after all: =+E2-INT(E42)+0.0078125 would be =E2-INT(E2)+0.0078125

A modification you might like: To change the result’s format from text to a number or custom number format, use the VALUE function as in VALUE(IF(MOD etc.), and set the cell’s format to the one desired. Note that the VALUE function won’t work if there is more than one space after any #s in the formula here, so be sure there is only one space. Actually, change all # symbols to zeros 0. Otherwise, Excel takes values less than 1 (such as 7/8) to mean something other than a fraction (perhaps date), and gives a bad result. If you custom format the cell to # #/##, the fraction will display properly without the zero despite it being in the formula. (Be sure not to miss the / symbol sandwiched in the fraction part #/##.)

So, the modified formula would be:
=VALUE(IF(MOD(INT(16*(E2-INT(E2)+0.03125)),16)=0,TEXT(E2,"0"),IF(MOD(INT(16*(E2-INT(E2)+0.03125)),8)=0,TEXT(E2,"0 0/2"),IF(MOD(INT(16*(E2-INT(E2)+0.03125)),4)=0,TEXT(E2,"0 0/4"),IF(MOD(INT(16*(E2-INT(E2)+0.03125)),2)=0,TEXT(E2,"0 0/8"),TEXT(E2,"0 0/16"))))))


Now, here's an alternate solution that you may enjoy. It requires far less characters.

To round E2 to the nearest 16th and show in lowest terms, format cell as # #/## and use:
=ROUND(E2/0.0625,0)*0.0625

To round E2 to the nearest 64th and show in lowest terms, format cell as # #/## and use:
=ROUND(E2/0.015625,0)*0.015625

Generally, to round E2 to the nearest Nth (example, N is 64) and show in lowest terms, format cell as # #/####### (Excel is not reliable showing fraction values from decimals when allowing more than 7 digits in the denominator), and use:
=ROUND(E2/(1/N),0)*(1/N)
This message was edited by nicknicknickandnick on 2003-02-08 23:53
This message was edited by nicknicknickandnick on 2003-02-08 23:57
This message was edited by nicknicknickandnick on 2003-02-09 00:15
This message was edited by nicknicknickandnick on 2003-02-09 00:19