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+1 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.
- Built-in fraction formats.
When you choose a fraction format, Excel finds the closest fraction.
- 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:
- Select the standard format for 16ths.
- 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.
- 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.
- Adapt this format for any fraction.
- Display numbers as 32ths.