Conversion Rounding issue with CM to In to (Ft and Inches).

[Dmichigan05]

I have an attachment for anyone whom can help me. I am looking to make the rounding method in the end of my function round these up instead of saying 1ft 12in. It will only happen right now when I use very specific centimeters meters or millimeters because the formula is recognizing the inches as a decimal of 11.8 and thus rounding to 12. Problem is the formula doesn't recognize when it's 12 inches that it needs to then say 2FT 0In. instead.

	CM		Weight Kgs		INCHES			FEET & IN		Weight LBS
Length	Width	Height	Length	Width	Height	Length	Width	Height
30	0	0	0	12	0	0	0' 11.8"	0' 0"	0' 0"	0
60	0	0	0	24	0	0	1' 11.6"	0' 0"	0' 0"	0
91	0	0	0	36	0	0	2' 11.8"	0' 0"	0' 0"	0
121	0	0	0	48	0	0	3' 11.6"	0' 0"	0' 0"	0
152	0	0	0	60	0	0	4' 11.8"	0' 0"	0' 0"	0
182	0	0	0	72	0	0	5' 11.7"	0' 0"	0' 0"	0
213	0	0	0	84	0	0	6' 11.9"	0' 0"	0' 0"	0

=INT(I4/12)&"' "&ROUND(MOD(I4,12),1)&"""" = the current formula in the feet and inches area.

 

[Fluff]

Can you please answer my question in post#8?

 

[Fluff]

Try the formula I sent

Not sure that works in all situations

 

[Bosquedeguate]

Not sure that works in all situations

Do you have an example?

 

[Dmichigan05]

Can you please answer my question in post#8?

Hello,
So The I is referring to the inches tab and when it's in the formula it is turning those inches in to feet and inches.

 

[Fluff]

But what is the formula you are using to convert from Cm to In?

 

[Bosquedeguate]

Book1
	A	B	C	D
1	CM	Inches		Round Ft & Inches
2	30	11.81102		1' 0"
3	60	23.62205		2' 0"
4	91	35.82677		3' 0"
5	121	47.6378		4' 0"
6	152	59.84252		5' 0"
7
8
9		10		0' 10"
10		10.1		0' 11"
11		10.1		0' 11"
12		10.1		0' 11"
13		10.1		0' 11"
14		10.1		0' 11"
15		10.1		0' 11"
16		10.1		0' 11"
17		10.1		0' 11"
18		10.1		0' 11"
19		11		0' 11"
20		12		1' 0"
21		25.7		2' 2"
Sheet1

Cell Formulas
Range	Formula
B2:B6	B2	=A2/2.54
D9:D21,D2:D6	D2	=INT((B2+0.9)/12)&"' "&INT(MOD(B2+0.9,12))&""""

 

[Dmichigan05]

Also @Fluff Can you please remove the little example now that you can see what I showed you so it's not up forever. I just wanted it to show you what I was working on since the extracting method isn't working to upload it. Thanks

 

[Fluff]

Please just answer my question.

 

[Rick Rothstein]

Does this formula give you the results you want (for all your values, not just the 60 cm one)...

=SUBSTITUTE(TEXT(A1/30.58,"0' 0/12\"""),"/12","")

Note: Change the A1 to the cell with your cm value in it.

 

[Dmichigan05]

Does this formula give you the results you want (for all your values, not just the 60 cm one)...

=SUBSTITUTE(TEXT(A1/30.58,"0' 0/12\"""),"/12","")

Note: Change the A1 to the cell with your cm value in it.

Hello Rick,
Yes the formula I have works for all of them for centimeters except when the decimal place makes it round to example being 1ft 12in. like the 60cm one. If i change the centimeter up or down it isn't an issue because the decimal place moves. I will work on these solutions you have all given and see if that fixes it for me. Thank you.