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.

 

[Dmichigan05]

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

The formula for Cm to In. is in the inches tab which is dividing by 2.54. Just like

Bosquedeguate just did in his solution.​

 

[Dmichigan05]

Is your question answered now @Fluff ? I think I answered all them. Thanks

 

[Fluff]

No, you have still not answered my question.
If you cannot be bothered to answer a very simple question, then I'm out.

 

[Bosquedeguate]

what happened when you tested this formula?

=INT((B2+0.9)/12)&"' "&INT(MOD(B2+0.9,12))&""""

 

[Dmichigan05]

what happened when you tested this formula?

=INT((B2+0.9)/12)&"' "&INT(MOD(B2+0.9,12))&""""

I am working on this one now I think it is the solution to my problem Let me test it with the other numbers and see what happens real quick.

 

[Rick Rothstein]

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.

But did you actually TRY the formula I posted in my message (Message #20)??? What you wrote gives me the impression that you didn't.

 

[Dmichigan05]

But did you actually TRY the formula I posted in my message (Message #20)??? What you wrote gives me the impression that you didn't.

Hello Rick. Just was trying your formula and for 60CM it works but when you put 61 or 62 it then changes the Inches portion to a decimal point. Thank you for the help on this.

 

[Dmichigan05]

I am working on this one now I think it is the solution to my problem Let me test it with the other numbers and see what happens real quick.

Do you know if If this same formula will work for M to in and MM to In. Or will I have to change anything for those calculations too?

 

[Rick Rothstein]

Hello Rick. Just was trying your formula and for 60CM it works but when you put 61 or 62 it then changes the Inches portion to a decimal point. Thank you for the help on this.

It does not do that for me. 61 and 62 both become 2' 0". I do not see how my formula could possibly return a decimal point unless perhaps their is some kind of locale setting issue I am not aware of. Where are you located at?

 

[Dmichigan05]

It does not do that for me. 61 and 62 both become 2' 0". I do not see how my formula could possibly return a decimal point unless perhaps their is some kind of locale setting issue I am not aware of. Where are you located at?

I am in the U.S.A. as well Rick. It does work now Rick this is also a good solution. I had to fix where your A1 was on my end I clicked the inches one instead of centimeters on my end. Your solution is a good solution as well. Do you know if your solution will work for meters and Millimeters as well? Or will it be a different formula? Thanks