MrExcel.com - Your One Stop for Excel Tips & Solutions

Display Fractions In Excel

December 18, 2001 - by

Do you remember sitting through Mr. Irwin's math class in 7th grade? That was where I learned about reducing fractions. You would be given an evil fraction like 135/864 and you would have to figure that it was really 5/32. And then there were the nasty compound fractions - where you would have to figure that 999/864 was 1 5/32. I ran across a nifty Excel custom number format this week, and I can not get out of my head how this will make the life of every 7th grader in the world much easier. This week, I will discuss fractions in Excel.

So, patronize me here -- I realize the surveys show that I have NO readers in 7th grade, but this is a unique opportunity to reach a new audience. I'll bring it back home in table 2 for my loyal business readers.

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