Formula converts value into Feet, Inches & Fraction of an Inch

M

Mark Main

If you've ever wanted to display Feet and Inches with Fractions of an
Inch, then this formula is for you. For example, this will change a
value of 15.125 to be 1' 3-1/8"

Type a number in A1, and paste this formula into B1:

=IF(A1<0,"("," ") & IF(TRUNC(ABS(ROUND(A1*16,0)/16)/
12,0)<1,"",TRUNC(ABS(ROUND(A1*16,0)/16)/12,0) & "'" & IF(MOD(ABS(A1),
12)=0, ""," ")) & IF(TRUNC(ABS(ROUND(A1*16,0)/
16)-12*TRUNC(ABS(ROUND(A1*16,0)/16)/12,0))>=1,
TRUNC(ABS(ROUND(A1*16,0)/16)-12*TRUNC(ABS(ROUND(A1*16,0)/16)/12,0)) &
IF(MOD(ABS(A1),1)=0,"","-"),"") & IF(MOD(A1,1)=0,"",MOD(ABS(A1),1)*16/
GCD(ROUND(ABS(A1)*16,0),16) & "/" & 16/ABS(GCD(ROUND(ABS(A1)*16,0),
16))) & IF(MOD(ABS(A1),12)=0,"","""") & IF(A1<0,")"," ")


It will reformat the number into Feet, Inches, and Fractions of
1/16". It will round the number to the nearest sixteenth inch.
Notice that it also places parentheses () around negative numbers.

If you want something rather than 16th of an inch, simply do a mass
change of 16 to 8ths or whatever you want. Be sure to leave the 12's
alone, they are for Feet.

Hope that it's useful to someone out there.

Mark
 
D

Duke Carey

With your formula, for 88.28125 i get

7' 4-4.50008000000003/16"

Here's a slightly shorter formula that*appears* to work a little better

=IF(A1<0,"(","")&INT(ABS(A1)/12)& "'
"&IF(MOD(ABS(A1),12)>0,INT(MOD(ABS(A1),12)),"")&IF(MOD(ABS(A1),1)>0,"-"&TEXT(MOD(ABS(A1),1),"??/??")&CHAR(34),""&CHAR(34))&IF(A1<0,")","")

For the same value it returns

7' 4- 9/32"

Interesting exercise
 
M

Mark Main

With your formula, for 88.28125 i get

 7' 4-4.50008000000003/16"

Here's a slightly shorter formula that*appears* to work a little better

=IF(A1<0,"(","")&INT(ABS(A1)/12)& "'
"&IF(MOD(ABS(A1),12)>0,INT(MOD(ABS(A1),12)),"")&IF(MOD(ABS(A1),1)>0,"-"&TEX­T(MOD(ABS(A1),1),"??/??")&CHAR(34),""&CHAR(34))&IF(A1<0,")","")

For the same value it returns

7' 4- 9/32"

Interesting exercise











- Show quoted text -

Sorry for the delayed response, I got really busy. You're right about
the bug. I fixed it. I like how short your version is... but my goal
was to round to the nearest 16th of an inch and this formula does
that. If the calculated fraction is a rounded approximate then a
tilde will be show on the right side.

Paste this formula below into cell B2 of Excel, and then enter any
number in inches into cell A1.

=IF(A1<0,"("," ") & IF(TRUNC(ROUND(ABS(A1)*16,0)/
16/12,0)<1,"",TRUNC(ROUND(ABS(A1)*16,0)/16/12,0) & "'" &
IF(MOD(ABS(A1),12)=0, ""," ")) & IF(TRUNC(ROUND(ABS(A1)*16,0)/
16-12*TRUNC(ROUND(ABS(A1)*16,0)/16/12,0))>=1,
TRUNC(ROUND(ABS(A1)*16,0)/16-12*TRUNC(ROUND(ABS(A1)*16,0)/16/12,0)) &
IF(MOD(ABS(A1),1)=0,"","-"),"") & IF(MOD(A1,1)=0,"", ROUND(MOD(ABS(A1),
1)*16,0)/GCD(ROUND(MOD(ABS(A1),1)*16,0),16) & "/" & 16/
GCD(ROUND(ABS(A1)*16,0),16)) & IF(MOD(ABS(A1),12)=0,"","""") &
IF(A1<0,")"," ") & IF(ROUND(MOD(ABS(A1),1)*16,0) = MOD(ABS(A1),1)*16,"
","~")


This formula assumes 16ths of an inch rounding accuracy… if you want
8ths an inch just change EVERY occurance of 16 to be an 8.

The formula also provides negative numbers inside parentheses ()
 
M

Mark Main

My original post had a bug (sorry), so I fixed it and reposted here.
I also posted this new formula as a reply a few threads down, but then
I thought that someone may not notice it, so I'm posting it higher up
on the thread to be seen easier.

ALSO, THIS FORMULA shows a tilde for any value that has to be rounded
to an approximation:


=IF(A1<0,"("," ") & IF(TRUNC(ROUND(ABS(A1)*16,0)/
16/12,0)<1,"",TRUNC(ROUND(ABS(A1)*16,0)/16/12,0) & "'" &
IF(MOD(ABS(A1),12)=0, ""," ")) & IF(TRUNC(ROUND(ABS(A1)*16,0)/
16-12*TRUNC(ROUND(ABS(A1)*16,0)/16/12,0))>=1,
TRUNC(ROUND(ABS(A1)*16,0)/16-12*TRUNC(ROUND(ABS(A1)*16,0)/16/12,0)) &
IF(MOD(ABS(A1),1)=0,"","-"),"") & IF(MOD(A1,1)=0,"", ROUND(MOD(ABS(A1),
1)*16,0)/GCD(ROUND(MOD(ABS(A1),1)*16,0),16) & "/" & 16/
GCD(ROUND(ABS(A1)*16,0),16)) & IF(MOD(ABS(A1),12)=0,"","""") &
IF(A1<0,")"," ") & IF(ROUND(MOD(ABS(A1),1)*16,0) = MOD(ABS(A1),1)*16,"
","~")


Paste the formula into B2 of Excel, and then enter any number in
inches into cell A1.
 
M

Mark Main

Sorry for the delayed response, I got really busy. You're right about
the bug.  I fixed it.  I like how short your version is... but my goal
was to round to the nearest 16th of an inch and thisformuladoes
that.  If the calculated fraction is a rounded approximate then a
tilde will be show on the right side.

Paste thisformulabelow into cell B2 of Excel, and then enter any
number ininchesinto cell A1.

=IF(A1<0,"("," ") & IF(TRUNC(ROUND(ABS(A1)*16,0)/
16/12,0)<1,"",TRUNC(ROUND(ABS(A1)*16,0)/16/12,0) & "'" &
IF(MOD(ABS(A1),12)=0, ""," ")) & IF(TRUNC(ROUND(ABS(A1)*16,0)/
16-12*TRUNC(ROUND(ABS(A1)*16,0)/16/12,0))>=1,
TRUNC(ROUND(ABS(A1)*16,0)/16-12*TRUNC(ROUND(ABS(A1)*16,0)/16/12,0)) &
IF(MOD(ABS(A1),1)=0,"","-"),"") & IF(MOD(A1,1)=0,"", ROUND(MOD(ABS(A1),
1)*16,0)/GCD(ROUND(MOD(ABS(A1),1)*16,0),16) & "/" & 16/
GCD(ROUND(ABS(A1)*16,0),16)) & IF(MOD(ABS(A1),12)=0,"","""") &
IF(A1<0,")"," ") & IF(ROUND(MOD(ABS(A1),1)*16,0) = MOD(ABS(A1),1)*16,"
","~")

Thisformulaassumes 16ths of an inch rounding accuracy… if you want
8ths an inch just change EVERY occurance of 16 to be an 8.

Theformulaalso provides negative numbers inside parentheses ()- Hide quoted text -

- Show quoted text -

I was told that people tend to put the "~" for approximation on the
left side of a number, and so this formula fixes that:

=IF(ROUND(MOD(ABS(A1),1)*16,0) = MOD(ABS(A1),1)*16," ","~") &
IF(A1<0,"("," ") & IF(TRUNC(ROUND(ABS(A1)*16,0)/
16/12,0)<1,"",TRUNC(ROUND(ABS(A1)*16,0)/16/12,0) & "'" &
IF(MOD(ABS(A1),12)=0, ""," ")) & IF(TRUNC(ROUND(ABS(A1)*16,0)/
16-12*TRUNC(ROUND(ABS(A1)*16,0)/16/12,0))>=1,TRUNC(ROUND(ABS(A1)*16,0)/
16-12*TRUNC(ROUND(ABS(A1)*16,0)/16/12,0)) & IF(MOD(ABS(A1),
1)=0,"","-"),"") & IF(MOD(A1,1)=0,"", ROUND(MOD(ABS(A1),1)*16,0)/
GCD(ROUND(MOD(ABS(A1),1)*16,0),16) & "/" & 16/GCD(ROUND(ABS(A1)*16,0),
16)) & IF(MOD(ABS(A1),12)=0,"","""") & IF(A1<0,")"," ")
 
T

TeaMan

I think this is really cool. I'm looking for a formula that leaves the
inches alone and converts the fraction to the nearest 1/16 of an inch.
Example 68.25 to 68-1/4". I'm not an excel wizard and it would take me a
month to figure out the formulas here. Can anyone help?

TeaMan
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top