Meters to Feet and Inches

[Coachcasa]

Hi everyone. I am a high school track coach in the U.S. and we are now going to start measuring our field events in metric rather than imperial. Since both myself and my coaching staff are so used to imperial, metric measurements don't mean a whole lot to us until we get used to them. So I'm creating a spreadsheet for my staff for quick reference. In column A I have meters and in column B I want to it to show the distance in feet and inches and I'd like the format to look like: 15' 7 ½". I want to round down to the nearest ½". Is there a way to do this? I can convert the meters to feet using the CONVERT formula, but then I get a decimal. I'm looking to do this:

Meters	Feet
1	3' 3"
1.05	3' 5 ½"
1.10	3' 7"

I don't know if this is possible. Thanks for your help.

 

[Joe4]

Have a look here: How to convert cm or m to feet and inches in Excel?

 

[kweaver]

Isn't 1.05 rounded down to the nearest 1/2 = only 3 feet 5 inches?

Dropdown_FJPunisher.xlsm
	A	B
1	Meters	Feet/Inches
2	1.00	3' 3"
3	1.05	3' 5"
4	1.10	3' 7"
5	1.78	5' 10"
Sheet6

Cell Formulas
Range	Formula
B2:B5	B2	=INT(CONVERT(A2,"m","ft"))&"' "&TEXT(ROUNDDOWN(12*(CONVERT(A2,"m","ft")-INT(CONVERT(A2,"m","ft"))),0.5),"0")&""""

 

[JamesCanale]

MrExcelPlayground.xlsm
	A	B
1	Meters	Feet
2	1	3' 3 1/2"
3	1.05	3' 5 1/2"
4	1.1	3' 7 1/2"
5	1.15	3' 9 1/2"
6	1.2	3' 11"
7	1.25	4' 1"
8	1.3	4' 3"
9	1.35	4' 5"
10	1.4	4' 7"
11	1.45	4' 9"
12	1.5	4' 11"
13	1.55	5' 1"
14	1.6	5' 3"
15	1.65	5' 5"
16	1.7	5' 7"
17	1.75	5' 9"
18	1.8	5' 11"
19	1.85	6' 1"
20	1.9	6' 3"
21	1.95	6' 5"
22	2	6' 6 1/2"
23	2.05	6' 8 1/2"
24	2.1	6' 10 1/2"
25	2.15	7' 0 1/2"
26	2.2	7' 2 1/2"
27	2.25	7' 4 1/2"
Sheet41

Cell Formulas
Range	Formula
B2:B27	B2	=INT(CONVERT(A2,"m","ft"))&"' "&TEXT(12*(CONVERT(A2,"m","ft")-INT(CONVERT(A2,"m","ft"))),"0 #/2")&""""
A3:A27	A3	=A2+0.05

 

[JamesCanale]

A bit more compact...

MrExcelPlayground.xlsm
	A	B
1	Meters	Feet
2	1	3' 3 1/2"
3	1.05	3' 5 1/2"
4	1.1	3' 7 1/2"
5	1.15	3' 9 1/2"
6	1.2	3' 11"
7	1.25	4' 1"
8	1.3	4' 3"
9	1.35	4' 5"
10	1.4	4' 7"
11	1.45	4' 9"
12	1.5	4' 11"
Sheet41

Cell Formulas
Range	Formula
B2:B12	B2	=INT(CONVERT(A2,"m","ft"))&"' "&TEXT(12*(MOD(CONVERT(A2,"m","ft"),1)),"0 #/2")&""""
A3:A12	A3	=A2+0.05

 

[jtakw]

Hi,

My version, final formula in F2
C, D, & E columns is just showing the various stages of the conversion, seems accuracy is within 1/2 ( 0.5 ) inch:

Book3.xlsx
	A		C	D	E	F
1	Meters
2	1		3.280839895	3' 3.37007874015748"	3' 3.37"	3' 3-1/2"
3	1.05		3.44488189	3' 5.33858267716536"	3' 5.34"	3' 5-1/2"
4	1.1		3.608923885	3' 7.30708661417323"	3' 7.31"	3' 7-1/2"
5	1.08		3.543307087	3' 6.51968503937008"	3' 6.52"	3' 6-1/2"
6	1.09		3.576115486	3' 6.91338582677165"	3' 6.91"	3' 7"
7	1.095		3.592519685	3' 7.11023622047244"	3' 7.11"	3' 7"
8	1.099		3.605643045	3' 7.26771653543307"	3' 7.27"	3' 7-1/2"
9	1.5		4.921259843	4' 11.0551181102362"	4' 11.06"	4' 11"
Sheet860

Cell Formulas
Range	Formula
C2:C9	C2	=CONVERT(A2,"m","ft")
D2:D9	D2	=INT(CONVERT(A2,"m","ft"))&"' "&MOD(CONVERT(A2,"m","ft"),1)*12&""""
E2:E9	E2	=INT(CONVERT(A2,"m","ft"))&"' "&ROUND(MOD(CONVERT(A2,"m","ft"),1)*12,2)&""""
F2:F9	F2	=INT(CONVERT(A2,"m","ft"))&"' "&TEXT(MOD(CONVERT(A2,"m","ft"),1)*12,"#-#/2")&""""

 

[kweaver]

Hi,

My version, final formula in F2
C, D, & E columns is just showing the various stages of the conversion, seems accuracy is within 1/2 ( 0.5 ) inch:

Book3.xlsx
	A		C	D	E	F
1	Meters
2	1		3.280839895	3' 3.37007874015748"	3' 3.37"	3' 3-1/2"
3	1.05		3.44488189	3' 5.33858267716536"	3' 5.34"	3' 5-1/2"
4	1.1		3.608923885	3' 7.30708661417323"	3' 7.31"	3' 7-1/2"
5	1.08		3.543307087	3' 6.51968503937008"	3' 6.52"	3' 6-1/2"
6	1.09		3.576115486	3' 6.91338582677165"	3' 6.91"	3' 7"
7	1.095		3.592519685	3' 7.11023622047244"	3' 7.11"	3' 7"
8	1.099		3.605643045	3' 7.26771653543307"	3' 7.27"	3' 7-1/2"
9	1.5		4.921259843	4' 11.0551181102362"	4' 11.06"	4' 11"
Sheet860

Cell Formulas
Range	Formula
C2:C9	C2	=CONVERT(A2,"m","ft")
D2:D9	D2	=INT(CONVERT(A2,"m","ft"))&"' "&MOD(CONVERT(A2,"m","ft"),1)*12&""""
E2:E9	E2	=INT(CONVERT(A2,"m","ft"))&"' "&ROUND(MOD(CONVERT(A2,"m","ft"),1)*12,2)&""""
F2:F9	F2	=INT(CONVERT(A2,"m","ft"))&"' "&TEXT(MOD(CONVERT(A2,"m","ft"),1)*12,"#-#/2")&""""

Maybe throw a TRIM in there as well?

 

[jtakw]

Thanks kweaver, Edited with a fix, no need for TRIM.

 

[Coachcasa]

Thanks everyone for your help! This is just what I needed.

 

[jtakw]

You're welcome, thanks for the feedback.