The formatted display of fractional measurements in Excel leaves a lot to be desired. This example and the formulas shown below were generously provided by Bernie Deitrick as a follow-up to a posting 2000-08-04 in the microsoft.public.excel.misc newsgroup. Bernie's formulas will be simplified after comments from Harlan Grove and corrections from by Ron Rosenberg on 2003-01-03
The corrections to be made will allow the numbers to be treated arithmetically, though they are being formatted strictly for display. The other formulas showing mixed feet, inches and fractional inches will still requidre something more than these simplified formulas, and will be text to allow feet and inch marks to appear next to figures even if strictly Feet and fractional feet.
Disadvantage of # #/## formatting, A1: 26 B2: =MROUND(A1,1/32) formatted as # #/## -- MROUND is in Analysis Tookpak displays in B2 as 26 0/1 as would the non Analysis Toolpak Function version B2: =ROUND(A1*32,0)/32 formatted as # #/##
---------------- Examples for 1/4, 1/8, 1/16, 1/32, 1/64 -----------
for 4ths
=ROUND(A1*4,0)/4 -- format as 0 #/##
for 8ths
=ROUND(A1*8,0)/8 -- format as 0 #/##
for 16ths
=ROUND(A1*16,0)/16 -- format as 0 #/##
for 32nds
=ROUND(A1*32,0)/32 -- format as 0 #/##
for 64ths
=ROUND(A1*64,0)/64 -- format as 0 #/##
formula for cell E7:
=INT(A1/12)&"' "&INT(MOD(A1,12))&""&IF(INT(A1)<>A1," " & TEXT(ROUND((A1-INT(A1/12)*12-INT(MOD(A1,12)))*128,0)/128,"##/###"),"")&""""
instead of his previous original beautiful formulas that is shown here to show some neat worksheet functions usage from his Aug 2000 examples: =INT(A1/12)&"' " & TEXT(MOD(A1,12),"0" &IF(ABS(MOD(A1,12)-ROUND(MOD(A1,12),0))>1/256, " 0/"&CHOOSE(MATCH(MIN(ABS(ROUND(MOD(A1,12)*{2,4,8,16,32,64,128},0)-MOD(A1,12)*{2,4,8,16,32,64,128})/{2,4,8,16,32,64,128}),ABS(ROUND(MOD(A1,12)*{2,4,8,16,32,64,128},0)-MOD(A1,12)*{2,4,8,16,32,64,128})/{2,4,8,16,32,64,128},0),2,4,8,16,32,64,128),""))&""""
A | B | C | D | E | |
1 | 26.0827 | Fr: Simple Decimal To: Simple Fractions |
Fr: Decimal Inches To: Fractional Inches |
Fr: Decimal Feet To: Inches and Fractional Inches |
Fr: Decimal Inches To: Feet, Inches and Fract. Inches |
2 | Down to 4ths | 26 | 26" | 26' 1" | 2' 2 0/4" |
3 | Down to 8ths | 26 1/8 | 26 1/8" | 26' 1" | 2' 2 1/8" |
4 | Down to 16ths | 26 1/16 | 26 1/16" | 26' 1" | 2' 2 1/16" |
5 | Down to 32nds | 26 3/32 | 26 3/32" | 26' 1" | 2' 2 3/32" |
6 | Down to 64ths | 26 5/64 | 26 5/64" | 26' 1" | 2' 2 5/64" |
7 | Down to 128ths | 26 11/128 | 26 11/128" | 26' 0 127/128" | 2' 2 11/128" |
8 | |||||
9 | This spreadsheet gives formulas used to display decimal values given in cell A1 as fractions with the denominator being variable and chosen as the smallest among 2,4,8,16.... as appropriate. Equations developed by Bernie Deitrick |
b1 | Fr: Simple Decimal To: Simple Fractions | |
b2 | 26 | =TEXT(A1,"0"&IF(ABS(A1-ROUND(A1,0))>1/8," 0/"&CHOOSE(ROUND(MOD(A1,1)*4,0),4,2,4),""))
=MROUND(A1,1/4) -- Analysis Toolpak Function -- format as 0 ##/## =ROUND(A1*4,0)/4 -- Without the Analysis Toolpak Function -- format as 0 ##/## |
b3 | 26 1/8 | =TEXT(A1,"0"&IF(ABS(A1-ROUND(A1,0))>1/16," 0/"&CHOOSE(ROUND(MOD(A1,1)*8,0),8,4,8,2,8,4,8),""))
=MROUND(A1,1/8) -- Analysis Toolpak Function -- format as 0 ##/## |
b4 | 26 1/16 | =TEXT(A1,"0"&IF(ABS(A1-ROUND(A1,0))>1/32," 0/"&CHOOSE(ROUND(MOD(A1,1)*16,0),16,8,16,4,16,8,16,2,16,8,16,4,16,8,16),""))
=MROUND(A1,1/16) -- Analysis Toolpak Function -- format as 0 ##/## |
b5 | 26 3/32 | =TEXT(A1,"0" &IF(ABS(A1-ROUND(A1,0))>1/64, " 0/"&CHOOSE(MATCH(MIN(ABS(ROUND(A1*{2,4,8,16,32},0)-A1*{2,4,8,16,32})/{2,4,8,16,32}),ABS(ROUND(A1*{2,4,8,16,32},0)-A1*{2,4,8,16,32})/{2,4,8,16,32},0),2,4,8,16,32),""))
=MROUND(A1,1/32) -- Analysis Toolpak Function -- format as 0 ##/## |
b6 | 26 5/64 | =TEXT(A1,"0" &IF(ABS(A1-ROUND(A1,0))>1/128, " 0/"&CHOOSE(MATCH(MIN(ABS(ROUND(A1*{2,4,8,16,32,64},0)-A1*{2,4,8,16,32,64})/{2,4,8,16,32,64}),ABS(ROUND(A1*{2,4,8,16,32,64},0)-A1*{2,4,8,16,32,64})/{2,4,8,16,32,64},0),2,4,8,16,32,64),""))
=MROUND(A1,1/64) -- Analysis Toolpak Function -- format as 0 ##/## |
b7 | 26 11/128 | =TEXT(A1,"0" &IF(ABS(A1-ROUND(A1,0))>1/256, " 0/"&CHOOSE(MATCH(MIN(ABS(ROUND(A1*{2,4,8,16,32,64,128},0)-A1*{2,4,8,16,32,64,128})/{2,4,8,16,32,64,128}),ABS(ROUND(A1*{2,4,8,16,32,64,128},0)-A1*{2,4,8,16,32,64,128})/{2,4,8,16,32,64,128},0),2,4,8,16,32,64,128),""))
=MROUND(A1,1/128) -- Analysis Toolpak Function -- format as 0 ##/## |
c1 | Fr: Decimal Inches To: Fractional Inches | |
c2 | 26" | =TEXT(A1,"0"&IF(ABS(A1-ROUND(A1,0))>1/8," 0/"&CHOOSE(ROUND(MOD(A1,1)*4,0),4,2,4),""))&"""" |
c3 | 26 1/8" | =TEXT(A1,"0"&IF(ABS(A1-ROUND(A1,0))>1/16," 0/"&CHOOSE(ROUND(MOD(A1,1)*8,0),8,4,8,2,8,4,8),""))&"""" |
c4 | 26 1/16" | =TEXT(A1,"0"&IF(ABS(A1-ROUND(A1,0))>1/32," 0/"&CHOOSE(ROUND(MOD(A1,1)*16,0),16,8,16,4,16,8,16,2,16,8,16,4,16,8,16),""))&"""" |
c5 | 26 3/32" | =TEXT(A1,"0" &IF(ABS(A1-ROUND(A1,0))>1/64, " 0/"&CHOOSE(MATCH(MIN(ABS(ROUND(A1*{2,4,8,16,32},0)-A1*{2,4,8,16,32})/{2,4,8,16,32}),ABS(ROUND(A1*{2,4,8,16,32},0)-A1*{2,4,8,16,32})/{2,4,8,16,32},0),2,4,8,16,32),""))&"""" |
c6 | 26 5/64" | =TEXT(A1,"0" &IF(ABS(A1-ROUND(A1,0))>1/128, " 0/"&CHOOSE(MATCH(MIN(ABS(ROUND(A1*{2,4,8,16,32,64},0)-A1*{2,4,8,16,32,64})/{2,4,8,16,32,64}),ABS(ROUND(A1*{2,4,8,16,32,64},0)-A1*{2,4,8,16,32,64})/{2,4,8,16,32,64},0),2,4,8,16,32,64),""))&"""" |
c7 | 26 11/128" | =TEXT(A1,"0" &IF(ABS(A1-ROUND(A1,0))>1/256, " 0/"&CHOOSE(MATCH(MIN(ABS(ROUND(A1*{2,4,8,16,32,64,128},0)-A1*{2,4,8,16,32,64,128})/{2,4,8,16,32,64,128}),ABS(ROUND(A1*{2,4,8,16,32,64,128},0)-A1*{2,4,8,16,32,64,128})/{2,4,8,16,32,64,128},0),2,4,8,16,32,64,128),""))&"""" |
d1 | Fr: Decimal Feet To: Feet and Fractional Inches | |
d2 | 26' 1" | =INT(A$1)&""&IF(INT(A$1)<>A$1," " & TEXT(ROUND((A$1-INT(A$1))*4,0)/4,"##/###"),"")&"""" |
d3 | 26' 1" | =INT(A$1)&""&IF(INT(A$1)<>A$1," " & TEXT(ROUND((A$1-INT(A$1))*8,0)/8,"##/###"),"")&"""" |
d4 | 26' 1" | =INT(A$1)&""&IF(INT(A$1)<>A$1," " & TEXT(ROUND((A$1-INT(A$1))*16,0)/16,"##/###"),"")&"""" |
d5 | 26' 1" | =INT(A$1)&""&IF(INT(A$1)<>A$1," " & TEXT(ROUND((A$1-INT(A$1))*32,0)/32,"##/###"),"")&"""" |
d6 | 26' 1" | =INT(A$1)&""&IF(INT(A$1)<>A$1," " & TEXT(ROUND((A$1-INT(A$1))*64,0)/64,"##/###"),"")&"""" |
d7 | 26' 0 127/128" | =INT(A$1)&""&IF(INT(A$1)<>A$1," " & TEXT(ROUND((A$1-INT(A$1))*128,0)/128,"##/###"),"")&"""" |
e1 | Fr: Decimal Inches To: Feet and Fractional Inches | |
e2 | 2' 2 0/4" | =INT(A1/12)&"' "&INT(MOD(A1,12))&""&IF(INT(A1)<>A1," " & TEXT(ROUND((A1-INT(A1/12)*12-INT(MOD(A1,12)))*4,0)/4,"##/##"),"")&"""" |
e3 | 2' 2 1/8" | =INT(A1/12)&"' "&INT(MOD(A1,12))&""&IF(INT(A1)<>A1," " & TEXT(ROUND((A1-INT(A1/12)*12-INT(MOD(A1,12)))*8,0)/8,"##/##"),"")&"""" |
e4 | 2' 2 1/16" | =INT(A1/12)&"' "&INT(MOD(A1,12))&""&IF(INT(A1)<>A1," " & TEXT(ROUND((A1-INT(A1/12)*12-INT(MOD(A1,12)))*16,0)/16,"##/##"),"")&"""" |
e5 | 2' 2 3/32" | =INT(A1/12)&"' "&INT(MOD(A1,12))&""&IF(INT(A1)<>A1," " & TEXT(ROUND((A1-INT(A1/12)*12-INT(MOD(A1,12)))*32,0)/32,"##/##"),"")&"""" |
e6 | 2' 2 5/64" | =INT(A1/12)&"' "&INT(MOD(A1,12))&""&IF(INT(A1)<>A1," " & TEXT(ROUND((A1-INT(A1/12)*12-INT(MOD(A1,12)))*64,0)/64,"##/##"),"")&"""" |
e7 | 2' 2 11/128" | =INT(A1/12)&"' "&INT(MOD(A1,12))&""&IF(INT(A1)<>A1," " & TEXT(ROUND((A1-INT(A1/12)*12-INT(MOD(A1,12)))*128,0)/128,"##/###"),"")&"""" |
A12: B2
B12: (pasted from B2)
C12: =GetFormula(indirect(A12))
The original section was converted to HTML with XL2HTMLX, and the formulas section was converted to HTML with XL2HTML with macros described on my HTML page.
Visit [my Excel home page] [Index page] [Excel Onsite Search] [top of this page]
Presentation in HTML format by David McRitchie
Appreciative comments should be directed to Bernie Deitrick mailto:deitbe@consumer.org for the content.
Please send your comments concerning formatting of this web page to: David McRitchie send email comments
Copyright © 1997 - 2004, F. David McRitchie, All Rights Reserved // portions within tables © Bernie Deitrick