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

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

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

Very nice!
--
Gary''s Student - gsnu2007k


"Mark Main" wrote:

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

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

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


"Mark Main" wrote:

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

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

On Aug 28, 2:41*pm, Duke Carey
wrote:
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



"Mark Main" wrote:
If you've ever wanted to displayFeetandIncheswith 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 intoFeet,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 forFeet.

Hope that it's useful to someone out there.

Mark- Hide quoted text -

- 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 ()

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

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.

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

On Oct 7, 7:37*pm, Mark Main wrote:
On Aug 28, 2:41*pm, Duke Carey
wrote:





With yourformula, for 88.28125 i get

*7' 4-4.50008000000003/16"

Here's a slightly shorterformulathat*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

"Mark Main" wrote:
If you've ever wanted to displayFeetandIncheswith Fractions of an
Inch, then thisformulais 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 thisformulainto 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 intoFeet,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 forFeet.

Hope that it's useful to someone out there.

Mark- Hide quoted text -

- 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 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,")"," ")

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

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

"Mark Main" wrote:

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.