Working with fractions

Some types of data, such as stock market quotes, normally display as fractions, not decimals. To enter a fraction in Excel, type the whole number (or integer) followed by a space, and then type the fraction, using a slash (for example, 5/8). If you type only a fraction, Excel may interpret it as a date (so it might read 5/8 as May 8). To avoid this mistranslation, enter 0, a space, and then the fraction.

When you enter a fractional value, Excel automatically applies a fraction number format that reduces it to the smallest possible denominator. For example, if you enter 16 2/8, Excel displays the number as 16 1/4. In some cases, however, you'll want the fractions to use a common denominator. For example, you might want the value 16 2/8 to be shown as 16 4/16. To obtain this result, select your cells and choose Format, Cells. Then select the Number tab and choose Fraction from the Category list. Finally, select the desired number format from the Type list.

The worksheet below shows some examples of numbers expressed as fractions. Column B shows the numbers produced using Excel's default formatting. Column C has the same values formatted as 16ths.

You can also express fractional data using a decimal point. For instance, the number 9 4/16 could appear as 9.04. Here, the digits to the right of the decimal represent 16ths. To display values in this format, use Excel's DOLLARFR() function. It's available only when the Analysis ToolPak is installed (select Tools, Add-ins to install it). The DOLLARFR() function takes two arguments: the number and an integer for the denominator. The formula =DOLLARFR(9.25,16), for example, returns 9.04.

This function is also useful for nondollar data. So, if you work with feet and inches, you can represent 11.5 feet as 11.06 (11 feet, 6 inches) by using this formula:

 =DOLLARFR(11.5,12). 

The value will then appear as "11 [feet] 6 [inches]."

The DOLLARFR() function is for display only. You can't use the value it returns in other calculations or in charts. To perform calculations on such values, reconvert them into decimal values by using the DOLLARDE() function (also part of the Analysis ToolPak).