Let's say you have the decimal value 5.708333 for feet in cell A1. To display feet and inches, use INT(A1) to get the 5 and use A1-INT(A1) to get the remainder (inches). Multiply the remainder by 12 and then use the TEXT function to display the value as "# #/#" inches. Here's the formula:
=INT(A1)&"' "&TEXT(12*(A1-INT(A1)),"# #/#")&CHAR(34)
13 comments:
Thanks, good info. How would you calculate the difference between 70'0" from 45'3"? I have excel displaying the feet inches with custom mask (###'##\"). So if I type in the cell 4503, the display shows 45' 03". Unfortunatly it does the math that way 7000-4503=2497 (24'97"). Thanks.
Barry
Thank you.
...there needs to be more @ Functions for us regular Joes. or at least as an Add-In.
This is a good try. only one thing: When I typed in the number20.7 the output returned 20' 8 2/5"
There isnt a 2/5 on the feet/inches scale. any work around there for that?
Alternatively, you could use custom format
#'" "#/12
which will display 5.3 as 5' 4/12 which is 5 feet 4 inches -- not perfect, but useful.
If you want to use fractions of an inch in standard format i.e. 1/4", 1/8", 1/16" etc. change the formula to:
=INT(A1)&"' "&TEXT(12*(A1-INT(A1)),"# #/4")&CHAR(34) OR
=INT(A1)&"' "&TEXT(12*(A1-INT(A1)),"# #/8")&CHAR(34) etc. to get the desired fraction conversion
Want to build antennas. The calculator gives results like "3ft5-15/32in" or "1ft3-5/8in". How can I proceed to have my results in meters ? My TI-30X2B (and I)turns warm !Is it an answer with Excel ? Many thanks for your help ! M.L.
If the value of A1 is in inches then this will convert the display to feet and inches rounding to the nearest 1/8":
=MROUND(A1,1/8)/12 & "' " & TEXT(MOD(MROUND(A1,1/8),12),"#-#/##") & """"
you can change the 1/8 to 1/16 for 16ths of an inch rounding.
This converts inches and decimal inches to feet, inches and fractions:
=INT(MROUND(A1,1/8)/12) & "' " & TEXT(MOD(MROUND(A1,1/8),12),"#-#/##") & """"
It rounds to 1/8 inche; but you can change the 1/8 to 1/16 if you like.
Also can use the following to convert decimal inches to feet and inches:
Make a column with the dimension in decimal inches (ie, 18.5). Keep the dimension correct as rounded to something reasonable like 1/16" (0.625 NOT 0.63).
Next to it use this formula:
=CONCATENATE((E13-MOD(E13,12))/12,"'-",ROUNDDOWN(MOD(E13,12),0),IF(MOD(E13,1),CONCATENATE(" ",TEXT(MOD(E13,1),"##/##"),""""),""""))
Replace the E13's in the formula with the cell ID with the decimal inches. Voila!
We figured this out when we were trying to format Design Tables for insertion into drawing sheets in Solidworks CAD system.
Use =INT(A1/12)&"' "&INT(12*(A1/12-INT(A1/12))) & "-" & TEXT(INT(MOD(12*(A1/12-INT(A1/12)),1)*16),"#")&"/16"&CHAR(34) to take decimal inches and convert to the nearest feet inches and 16th of inch. Such as 657.643 inches will result in
54' 9-10/16"
Assumes A1 is cell with decimal inches.
While all of these formulas are mathematically correct, there is a huge "0" missing from these displays. 9'- 1/2" is confusing to the point where it is not productive. Solve for the missing "0" when the length involves feet and inches and the inches is under 1.
=INT(MROUND(A4,1/16)/12) & "'-" & TEXT(MOD(MROUND(A4,1/16),12),"0 #/##") & """"
This adds the zero from the 1:09pm post
Fantastic!
Post a Comment