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 ]

## Like this thread? Share it with others