Use of Fractions in Excel

Location:   http://www.mvps.org/dmcritchie/excel/fraction.htm
Home page: http://www.mvps.org/dmcritchie/excel/excel.htm
[View without Frames]

Excel will not break down the fraction to the LCD if you use sixteenths and it may choose 1 or 2 digit divisors that you would not have chosen for fractional inches.
    i.e.   1/7, 2/9, 4/16 ...

 ABCDEFG
1 descript 1 digit 2 digits 3 digits halves quarters sixteenths
2 format # ?/? # ??/?? # ???/??? # ?/2 # ?/4 # ??/16
3 11.04 11 11 1/25 11 1/25 11 11 11 1/16
4 11.11 11 1/9 11 10/91 11 11/100 11 11 11 2/16
5 11.12 11 1/8 11 3/25 11 3/25 11 11 11 2/16
6 11.16 11 1/6 11 4/25 11 4/25 11 11 1/4 11 3/16
7 11.2 11 1/5 11 1/5 11 1/5 11 11 1/4 11 3/16
8 11.24 11 1/4 11 6/25 11 6/25 11 11 1/4 11 4/16
9 11.28 11 2/7 11 7/25 11 7/25 11 1/2 11 1/4 11 4/16
10 11.32 11 1/3 11 8/25 11 8/25 11 1/2 11 1/4 11 5/16
11 11.31 11 1/3 11 9/29 11 31/100 11 1/2 11 1/4 11 5/16
12 11.32 11 1/3 11 8/25 11 8/25 11 1/2 11 1/4 11 5/16
13 11.33 11 1/3 11 1/3 11 33/100 11 1/2 11 1/4 11 5/16
14 11.34 11 1/3 11 17/50 11 17/50 11 1/2 11 1/4 11 5/16
15 11.52 11 1/2 11 13/25 11 13/25 11 1/2 11 2/4 11 8/16
16 11.56 11 5/9 11 14/25 11 14/25 11 1/2 11 2/4 11 9/16
17 11.6 11 3/5 11 3/5 11 3/5 11 1/2 11 2/4 11 10/16
18 11.64 11 2/3 11 16/25 11 16/25 11 1/2 11 3/4 11 10/16
19 11.68 11 2/3 11 17/25 11 17/25 11 1/2 11 3/4 11 11/16
20 11.69 11 2/3 11 20/29 11 69/100 11 1/2 11 3/4 11 11/16
21 11.76 11 3/4 11 19/25 11 19/25 12 11 3/4 11 12/16
22 11.8 11 4/5 11 4/5 11 4/5 12 11 3/4 11 13/16
23 17.4 17 2/5 17 2/5 17 2/5 17 1/2 17 2/4 17 6/16

Formula to show feet/inches from stored value in inches

    =INT(206.4/12)&"'  "&ROUND(MOD(206.4,12),1)&""""

Formula to show feet/inches from stored value in feet

    =INT(17.2)&"'  "&ROUND(12*MOD(17.2,1),1)&""""
replace the 206.4 or 17.2 with your cell address i.e. B2

Formulas to show feet/inches + fractional inches

   =INT(206.4/12)&"'  "&Text(MOD(206.4,12),1),"# ##/##")&""""
   =INT(17.2)&"'  "&Text(12*MOD(17.2,1),"# ##/##")&""""

Example

Format --> cells --> custom

  VALUE =GetFormat(A...) =GetFormula(A...) +
  A B C D
16 23 1/2" # ??/??\" 23.5 N
17 72° 14' 32" [h]° mm' ss\" =72.2422 / 24 F
18 17'  2.4" General =INT(17.2)&"'  "&ROUND(12*MOD(17.2,1),1)&"'" F
19 17'  2.4" General =INT(206.4/12)&"'  "&ROUND(MOD(206.4,12),1)&"'" F
20 17.256    0.????_);(0.????);0.????;@ 17.256 N
21 4 lb. 2.0 oz. General =INT(4.125)&"lb. "&ROUND(16*MOD(4.125,1),1)&"oz." F
22 1' 8 13/25" General =INT(17.71)&"' "&TEXT(12*MOD(17.71,1),"# ##/##")&"""") F
23 1' 8 1/2" General =INT(17.71)&"' "&TEXT(12*MOD(17.71,1),"# #/#")&"""") F

One way to simplify creating custom formats is to pick the best match say under number or fractions.  Then keeping the same cell selected go to custom format.  Also in custom note that as you change the formatting the example will change.

This page is an expanded version of the fractional information found my Formula page, "Show FORMULA of another cell in Excel".

See page on Fractions and Formatting of Fractions by Bernie Deitrick.«

You are one of many distinguished visitors who have visited my site here or in a previous location  since opening on January 1, 1998.    This document is about 3 pages if printed.

Visit my Excel home page, or my Excel Site Index    [Site Search -- Excel] 

Please send your comments concerning this web page to: David McRitchie send email comments

Copyright © 1997 - 2004,  F. David McRitchie,  All Rights Reserved