- Worldstart's Tech Tips Newsletter - http://www.worldstart.com -
Fractions in MS Excel
Posted By On December 30, 2009 @ 10:12 AM In MS Excel | Comments Disabled
Now that we’ve seen the basics, let’s look at a couple of special cases.
First, let’s discuss the case of simple fractions (fractions without a whole number).
In order to get Excel to accept simple fractions and actually treat them as fractions, you’ll need to:
Enter a 0 (zero) as the whole number
Followed by a space
The fraction numerator
A slash (/)
The fraction denominator
If you don’t enter the whole number 0, Excel will turn your fraction into a date and you don’t want that! (For many people, fractions are confusing enough without throwing unwanted dates into the mix!)
The second special case I wanted to discuss involves fractions containing three digit numerators or denominators.
In order to get Excel to keep the larger numbers, you’ll need to format the cells appropriately.
I could get the program to accept fractions with two digit numerators or denominators, but once I bumped it up to three digits, the program automatically reduced the fraction back to a two digit number. This even happened in cases where the fraction could not be mathematically reduced. It simply approximated as close at it could to the original entered data.
So, I went looking.
In older versions of Excel you need the Format menu, Cells choice.
In Excel 2007 you would want the Home tab of the Ribbon, Number section, then choose More Number Formats from the bottom of the Number Format drop-down list.
But, if you really want to make life easy, Ctrl + 1 works in all of them.
Once the Format Cells dialog box is opened you simply need to set the fraction type as “Up to three digits” and then click OK.
If you try to format a cell before entering a fraction then you may have the Format Cells dialog box open with a setting of a General number. Simply choose Fraction from the category list on the left and you’ll be back in business.
Now, you can use fractions in your formulas, in many cases making the results more exact. I mean, let’s face it, we tend to round the decimals after a few places and Excel won’t do that during the calculations. That is, unless we force it to.
That’s it. Excel fractions made easy!
Article printed from Worldstart's Tech Tips Newsletter: http://www.worldstart.com
URL to article: http://www.worldstart.com/fractions-in-ms-excel/