Inches to Feet-Inches 16

[dik]

Does anyone have a formula to convert inches to feet-inches, eg. 96 is converted to 8'-0"? If you do, can you cut and paste it?

thanks, Dik

 

[Clyde38]

Try:

=INT(F12/12)&"'"&"-"&MOD(F12,12)&""""

 

[dik]

Will do... thanks, Dik

 

[chichuck]

that works but it gives you a decimal for the inches. Go one step further to get the fractions:

format the cell for the inches using the fractions type under format/cells/number/catetory/fractions.

It is a little inelegant in that when you choose eighths you get 2/8 and not 1/4, but it is something.


regards,


chichuck

 

[francesca]

If you choose "allow two digits" you'll get 1/12, 1/8, 1/4, 1/2 and combinations thereof. However, this still isn't feet and inches, it's feet and fractions of feet. On the up side, however, the field remains formatted as a number.

 

[fglass]

So, is there a way to convert that inches in feet-inches to fraction?

 

[ivymike]

it's easy if you want everything in 32ths (or any other fraction), but more complicated if you want to do halves, 4ths, 8ths, 16ths, depending on the decimal.

=INT(F12/12)&"'"&" - "&INT(MOD(F12,12))&" "&ROUND((F12-(INT(F12/12)*12)-INT(MOD(F12,12)))*32,0)&"/32 """

 

[fglass]

ivymike, I have tried using your equation and doubt that the solution comes out right.

I wonder if anybody know the way to convert the fractions to 16ths? If possible, it would automatically convert the 16ths to simplest fractions as well. For instance, 8/16 to 1/2. Any genius outthere?

 

[francesca]

Excel has a built-in "fractions" number type. If you pick the "up to two digits" type (and don't force 8ths, 16ths, etc.) then Excel will magically conver to the simplest fraction. (E.g. 98/12 = 8 1/6.)

Converting 98 inches to 8'2" however is achievable in a few steps, but the output will be a text field, not a number field. You can create a column with just the inches component by using the formula: =MOD(A1,12). Assuming 98 inches is in cell A1, that formula will return a value of 2. Extracting the feet involves the INT formula: =INT(A1/12). You now have one column with the feet, another with the inches.

If you're not picky, and you're just looking for presentation, then you can use custom formatting to make them look correct. Format Cell --> Custom --> type in the box [0"ft"] or if you want the space first, [0" ft"]. Similarly for the inches. You can use the ' and '' (use two single quotes) marks if you like, and you can also put a - before the inches ["- "0" in"]. Just put whatever text you want in quotes, and remember double quotes inside double quotes will not work.

If you want the ft and in in one field, then you have to give up the number quality. (But you can preserve the decimal number in a hidden field for calculation purposes.)
Clyde's solution above should work.

 

[dbuzz]

Wouldn't it be easier if you Americans just started using the metric system like the rest of us!

 

[dik]

We Canadians use metric and imperial interchangeably... about 99% of our firm's work is imperial... It's the cost of conversion that has kept the Americans using imperial... The luxury of metric is pricey... IMHO

Dik

 

[bpeirson]

Here is a user defined function which returns an imperial fraction from any decimal number. I do not know if it formats as text but I do know that it can be referred to as a number in a formula. If the imperial fraction is in cell A1 and I put the formula "=A1*1" in cell B1 then I get the decimal equivalent of the imperial fraction, not the original decimal value.

A modification should allow any value over 12 to display feet as well as inches.

Public Function Imp_Frac(ByVal Dec_Arg) As String
Dim Num As Long, sixteenths As Integer

Num = Fix(Dec_Arg)
sixteenths = Abs(Fix((Dec_Arg - Num) * 16))
Select Case sixteenths
Case 1, 3, 5, 7, 9, 11, 13, 15
Imp_Frac = Num & " " & sixteenths & "/16"
Case 2, 6, 10, 14
Imp_Frac = Num & " " & sixteenths / 2 & "/8"
Case 4, 12
Imp_Frac = Num & " " & sixteenths / 4 & "/4"
Case 8
Imp_Frac = Num & " 1/2"
Case Else
Imp_Frac = Num
End Select

End Function

 

[ivymike]

Fglass
So, is there a way to convert that inches in feet-inches to fraction?

I interpreted the above to mean "is there a way to convert inches to feet-inches where the inches are expressed as a fraction." Here are some inputs and outputs:

96 8' - 0 0/32 "
96.25 8' - 0 8/32 "
50 4' - 2 0/32 "
50.125 4' - 2 4/32 "
112 9' - 4 0/32 "
112.125 9' - 4 4/32 "
115.35 9' - 7 11/32 "

Perhaps if you express the problem clearly you will get more helpful responses.

 

[ivymike]

using francesca's suggestion:
96 8' - 0"
96.25 8' - 1/4"
50 4' - 2"
50.125 4' - 2 1/8"
112 9' - 4"
112.125 9' - 4 1/8"
115.35 9' - 7 7/20"

=INT(F12/12)&"'"&" - "&TEXT(MOD(F12,12),"# #/##")&""""

 

[yakpol]

Here's my inches to feet-inch-fractions formula. It provides 1/16 fractions, but will show 1/8 1/4 1/2 if they are closest. For example 90.375 = 7'-6 3/8"

=INT(A1/12)&"'-"&INT(MOD(A1,12))&IF(A1-INT(A1)=0,""""," "&TEXT(FLOOR(A1-INT(A1),1/16),"?/??")&"""")

 

[yakpol]

A little refinement to take care of negative values:

=IF(SIGN(A1)=1,"","- ")&INT(ABS(A1)/12)&"'-"&INT(MOD(ABS(A1),12))&IF(ABS(A1)-INT(ABS(A1))=0,""""," "&TEXT(FLOOR(ABS(A1)-INT(ABS(A1)),1/16),"?/??")&"""")

 

[TampaBridgeDesign]

yakpol-
Using the code you've provided, I typed 22.02 into cell A1. This gives the following result: 1'-10 0/1 "

Why does it not give 1'-10 0/16" or just 1'-10" ?

 

[joerd]

You'd need to check if the 1/16th fraction is giving a 0 - the formula now checks to see if there is a fraction at all.
I also changed the SIGN part, to show 0'-0" if there is a value of 0 entered, instead of - 0'-0".

=IF(SIGN(A1)=-1,"- ","")&INT(ABS(A1)/12)&"'-"&INT(MOD(ABS(A1),12))&IF(FLOOR(ABS(A1)-INT(ABS(A1)),1/16)=0,""""," "&TEXT(FLOOR(ABS(A1)-INT(ABS(A1)),1/16),"?/??")&"""")

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[yakpol]

joerd,
Thanks for fixing the formula, it seems to be bulletproof now. Great result of combined efforts. Thank you dik for bringing up interesting problem!

yakpol

 

[boldfish]

Be aware lest any unintended consequences occur -> the formula suggested by joerd returns 0'-0 7/16" when .499 is entered in A1.