Excel: Converting Decimal Numbers into Fractions

 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,"",TEXT(INT(ref),"0")&VLOOKUP(ref-INT(ref),table_range,2))
where "ref" is a reference to the cell that contains the decimal number and "table_range" 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,"",TEXT(INT(A1),"0")&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 "table_range" 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 "table_range".

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.

KBCategory: kbother
KBSubcategory:

Additional reference words: 1.0 1.00 1.03 1.04 1.06 1.5 1.50 2.0 2.00
2.01 2.1 2.10 2.2 2.20

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: November 30, 1994
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.