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]

Hi & welcome to MrExcel.

Can you re-post your sample data using the XL2BB add-in, that way we know which columns are which & where all your formulae are.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

formula is recognizing the inches as a decimal of 11.8

What formula, there is nothing there that shows 11.8

 

[Dmichigan05]

Hi & welcome to MrExcel.

Can you re-post your sample data using the XL2BB add-in, that way we know which columns are which & where all your formulae are.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.


What formula, there is nothing there that shows 11.

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

This is the formula in the Feet and Inches tab. The 11.8 inches area where I want it to instead round to 12 inches round to the next feet. Example being 60 CM is 24 inches when rounded. When rounded to Feet and Inches it 1' 11.6" I can change the formula to round to whole number but then it will just read as 1'12" when I want it to read 2' 0"

Your tool is noted I am working extracting that file to use that feature. Thanks

 

[Fluff]

But what value is in I4 the data you posted does not show which column is which.
Also if I use your formula on a value of 24 inches I get 2' 0""

 

[Bosquedeguate]

Do you want 11.5 to 11.9 inches rounded up to 1 ft?
And 11.1 to 11.4 inches rounded down to 11 inches?

 

[Dmichigan05]

Do you want 11.5 to 11.9 inches rounded up to 1 ft?
And 11.1 to 11.4 inches rounded down to 11 inches?

Hello,
Thank you for the question. No I would want it to show feet and inches, and if it is 11.1 inches it would need to round to the next foot.

 

[Dmichigan05]

But what value is in I4 the data you posted does not show which column is which.
Also if I use your formula on a value of 24 inches I get 2' 0""

I understand I am working on getting this setup to extract so I can upload the spreadsheet for you. I just sent the above in the meantime to see if anyone can come up with a solution before I get this extracting the file to work. Thanks

 

[Fluff]

Is I4 a formula? if so what is it?

 

[Bosquedeguate]

try this formula
my values in inches like 11.9 were in column A (so you will need to change the formula for your sheet (I think column "I")

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

 

[Bosquedeguate]

View attachment 96809

I was able to take a screen shot of what I am working with so you can see a little bit more information to what I am doing here.

Try the formula I sent