Fractions in Excel

The new number format is in the form of "# ??/???". The simple spreadsheet at the right illustrates the fraction reducing example. Plug the numerator in A1, the denominator in A2. Excel turns the fraction into a decimal in A3. A4 is the same number, but formatted as "# ??/???". The "#" indicates that in a mixed fraction, the integer should appear first, followed by a fraction. I guess Mr Excel is easily impressed, but this amazes the heck out of me.

To assign the custom format, put the cell pointer in A4, Format - Cells - Number - Custom, and then type # ??/??? in the Type: box.

OK, end of the 7th grade math lesson. Other uses more relevant in business:

• Stock quotes? Use the "# ??/??" format to report in up to 32nds.
• If you need to always report in 8ths, use "# ?/8"
• If you need to always report in 10ths, use "# ?/10"

A few weeks after this was published, Michael wrote: "I am using a formula to calculate the radius of an arc. The answer has to be in fractions of an inch down to the nearest 16th. However, I need to reduce 8/16's to 1/2, 10/16 to 5/8, etc." Wow! I could not find a great answer to this. Here is a situation where being able to change the number format in a conditional format would make sense, but that is not available. If the result of the formula is in cell B2, you could put the following formula in C2 to correctly display your result:

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