When you think about entering a fractional value in Excel, you may be tempted to convert the fraction from its original base value to a Base 100 value.
THE LONG, HARD WAY (most people’s way)
Suppose you measure the height of your child on a door every birthday and you want to calculate the average height grown per year.
Year 2 – 33”
Year 3 – 35 5/8”
Year 4 – 37 1/2”
Year 5 – 39 3/8”
Year 6 – 41 3/4”
Year 7 – 43 5/16”
Year 8 – 46”
Year 9 – 48 1/4”
Year 10 – 50 9/32”
Because we learned in school that in order to add these values together, we must first set the denominators to the same value.
We now painstakingly determine that 32 is the lowest common denominator (LCD) and convert all of the values to the following:
Year 2 – 33 0/32”
Year 3 – 35 20/32”
Year 4 – 37 16/32”
Year 5 – 39 12/32”
Year 6 – 41 24/32”
Year 7 – 43 10/32”
Year 8 – 46 0/32”
Year 9 – 48 8/32”
Year 10 – 50 9/32”
But wait, that’s not how we need to enter the values into Excel. Don’t we need to convert the “32s” into “100s”? If we divide 100 by 32, we will get a value of 3.125. We can now multiply all of the numerators by this new value to achieve the following list:
Year 2 – 33.0
Year 3 – 35.625
Year 4 – 37.5
Year 5 – 39.375
Year 6 – 41.75
Year 7 – 43.3125
Year 8 – 46.0
Year 9 – 48.25
Year 10 – 50.28125
Now you are in a position to run calculations against the data (i.e. Average height grown per year equals 2.16”.)
Now we have to perform the opposite conversion to get the number back into fractional inches.
The faster, easier way of doing the same thing
You don’t have to do ANY of that work to achieve the same result. When you enter a fraction, simply type the whole number followed by a space, then type the fraction as you would have read from the original measurement regardless of the denominator’s value.
In reality, Excel is actually storing the “100’s” version of the numbers and running all of the calculations against those numbers instead.
Notes on Excel’s Automatic Adjustments
1) Excel will always reduce the fraction to its lowest terms (i.e. 8/16 will be reduced to 1/2).
2) If your whole number is a zero, do not simply enter the fraction; Excel will interpret that as a date (i.e. 1/2 will be interpreted as January 2nd). Enter a zero in the whole number position followed by a space and the fraction (i.e. 0 1/2).