Feet & Inches Function

[hcaoc7]

I am looking for a function that will convert inches into feet and inches. I would like to be able to put for example 77.75 inches in and get 6'5 3/4". Can anybody help with this problem? Thanks.

 

[Barrie Davidson]

=INT(A1/12)&"'  "&TRIM(TEXT(A1-INT(A1/12)*12,"# ???/???"))&""""

Regards,

 

[just_jon]

Gawd this is pug-ugly, but --

Book2
	A	B	C	D	E
1	77.75	6'53/4"
2
3
4
Sheet1

*limited* testing only...

B1: =INT(A1/12)&"' "&LEFT(12*((A1/12)-INT(A1/12)),FIND(".",12*((A1/12)-INT(A1/12)))-1)&" "&TEXT(MID(12*((A1/12)-INT(A1/12)),FIND(".",12*((A1/12)-INT(A1/12))),255),"#0/#0")&""""

 

[hcaoc7]

just_jon

Thanks for the help! How do I get it to display just a number with no fraction now. I should have included this the first time. I am testing kids in several different tests in a weights class. I measure them in inches and then would like to put that information into a spreadsheet and have it convert to feet and inches. Your formula worked great as long as there is a fraction involved, if there is not a fraction involved I get the value error. How can I put in 77" and get back 6'5".

Thanks.

 

[just_jon]

Use Barrie's (far superior) formula -- mine will error-out on integer-only data

 

[hcaoc7]

Barrie

Thanks just what I was looking for!!

 

[lenze]

Try this approach, using the DOLLARFR function(Analysis Tool Pack)

Book1
	A	B	C	D
1
2	77		6ft.05in
3
Sheet1

Note the custom formatting of C2

 

[mchase]

Hey Barrie,

I tried your formula for feet & inches and LOVE IT , but ... it brings back WIERD fractions (i.e. 201/304) . I'm converting from metric mm to decimal feet, then using your formula

=INT(B2/12)&"' "&TRIM(TEXT(B2-INT(B2/12)*12,"# ???/???"))&""""

to convert to f' ii" format.

How can I specify the denominator (x/2, x/4, x/8, x/16, x/32, x/64) I want it to round up/down too? I would like the option to specify which denomination to round to, sometimes I want to go to the 1/16" but mostly I only need it to go to the 1/2" or 1/4".

Also, would it ONLY round up or down? Is there a way to round to the closest denomination?

 

[mchase]

Hey Barrie,

Thanks for the help. I found some better results using a feet-inch.xls from steel link [http://steel-link.com/2002/downloads/other.cfm]

This is an Microsoft Excel file with visual basic functions for working in feet and inches. If you work with architectural dimensions in a spreadsheet they come in handy. Submitted by Mark Johnson.

I modified the formula a bit, but this gave me exactly what I was looking for (and was acurate).

=dftos(MROUND(G4/304.8*12,1/8)/12)

To convert string dimension to decimal feet use function stodf() [StringToDecimalFeet].
Example:
Cell B5 contains a dimension in feet and inches, cell D5 uses function stodf() to convert it to decimal feet.

24'-5 5/16" 24.44270706

To convert decimal feet to a string dimension use function dftos() [DecimalFeetToString].
Example:
Cell B11 contains a number which represents decimal feet, cell D11 uses function dftos() to convert it to a string dimension.

16.57795 16'-6 15/16"

You can do math with them.
Result1 is simply adding dimensions and displays result in decimal feet.
Result2 nests the functions to display the same operation in string dimension.

Dist1 Dist2 Result1 Result2
16'-6 15/16" 24'-5 5/16" 41.02083206 41'-0 1/4"

I am using this feature, plus MROUND() and ROUND() to convert from metric mm to Imperial feet and inches
Example1:
Cell B30 is the metric length in mm
Cell D30 uses take the value of B30 devides by 304.8 to convert to decimal feet
Cell D31 takes the value of D31 and multiplies by 12 for the decimal value of inches
Cell F31 uses function MROUND() to round the decimal to the closest fraction denominator (x/2, x/4, x/8, x/16, x/32, etc)
Cell H31 multiplies F31 by 12 to convert back to decimal feet
Cell J31 uses formula dftos() to convert H31 to a string dimension.

1910 6.266404199 6'-3 13/64"
75.19685039 75.1875 6.265625 6'-3 3/16"
Example 2:
Cell B35 is the metric length in mm, Cell D35 uses the formula =dftos((MROUND(((B35/304.8)*12),1/16))/12) to achieve the same results.

1910 6'-3 3/16"

Anyway, this is an FYI for future use.

Thanks,

Michael

 

[mora]

Hi, I was wondering the steps to take to convert feet to feet inches