Microsoft KB Archive/27524

{| = Excel: Converting Decimal Numbers into Fractions =
 * width="100%"|

Last reviewed: November 30, 1994

Article ID: Q27524 The information in this article applies to:
 * Microsoft Excel for Windows, versions 2.0 and 2.1
 * Microsoft Excel for the Macintosh, versions 1.0, 1.5 and 2.2

SUMMARY
Microsoft Excel versions 2.2 and earlier cannot format decimal numbers as fractions and still maintain the entries as numbers. Later versions of Excel do not have this limitation. Excel can convert a decimal number into a text string that represents its fractional equivalent. To do this, use the formula

=IF(ref=0,&quot;&quot;,TEXT(INT(ref),&quot;0&quot;)&VLOOKUP(ref-INT(ref),table_range,2)) where &quot;ref&quot; is a reference to the cell that contains the decimal number and &quot;table_range&quot; is the table that contains a column of decimal numbers and a column of the fraction equivalents of the decimal number. For example, the formula

=IF(A1=0,&quot;&quot;,TEXT(INT(A1),&quot;0&quot;)&VLOOKUP(A1-INT(A1),$C$1:$D$8,2)) entered into cell B1 on a worksheet can be set up as follows: A1: 5.25 B1: 5 1/4 C1: 0.0    D1: A2:     B2:        C2: 0.125  D2: 1/8 A3:     B3:        C3: 0.25   D3: 1/4 A4:     B4:        C4: 0.375  D4: 3/8 A5:     B5:        C5: 0.5    D5: 1/2 A6:     B6:        C6: 0.625  D6: 5/8 A7:     B7:        C7: 0.75   D7: 3/4 A8:     B8:        C8: 0.875  D8: 7/8 For the VLOOKUP function to work properly, the &quot;table_range&quot; must be sorted in ascending order. Using the above table, the decimals can be converted to their fractional equivalents to the nearest 1/8. For greater precision, include more fraction equivalents in the &quot;table_range&quot;. Note: To enter a fraction as text in a cell in the table, enter a space before the fraction. If a space is not included, Excel may interpret the fraction as a date (for example, 1/5 would be interpreted as January 5 of the current year). Also note that the converted fractions are no longer numbers and cannot be manipulated as such; they are purely for display purposes.
 * }