This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
Show Results as Fractions
Problem: I work in an industry that reports values in fractions. Stockbrokers used to deal in increments of 1/8, and tire engineers still measure tread depth in increments of 1/32 inch.
Strategy: There are number formats for fractions. When you press Ctrl+One to display the Format Cells dialog, you will see that there are nine standard fraction formats available in the Number tab of the Format Cells dialog box.
Figure 1163 Built-in fraction formats.
When you choose a fraction format, Excel finds the closest fraction.
Figure 1164 0.548679 is about 5/9 or 45/82.
Beyond the seven shown above, Excel offers standard formats for 10ths and 100ths. Unfortunately, there is not a standard format for 32ths.
You can create a custom numeric format to handle 32ths:
1. Select the standard format for 16ths.
2. In the Category list on the Number tab of the Format Cells dialog, scroll down and select Custom. The custom number format code for 16ths is # ??/16. From this, you can deduce that # ??/32 might be a valid number format.
3. Click in the Type box and change the 16 to 32. The Sample area will immediately confirm that you have hit upon the correct format for 32ths.