| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 () |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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,")"," ") |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| formula to convert decimal feet to feet inch sixteenths | Excel Discussion (Misc queries) | |||
| Display feet inches and fraction | Excel Discussion (Misc queries) | |||
| Is there a formula for inches to feet and inches? | Excel Worksheet Functions | |||
| decimal feet to feet-inch-sixteenths conversion | Excel Discussion (Misc queries) | |||
| Need formulas for adding feet, inches & fraction in one cell | Excel Worksheet Functions | |||
Linear Mode
