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]

@Fluff were you able to help me delete comment 10 for me please. Thank you for all your help today.

 

[Rick Rothstein]

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

It will be a different formula. I am away from my computer right now... I will look at it when I get back to my computer. However, show me a sample of how the converted value should look (I am interested in how the parts should be labeled).

 

[Dmichigan05]

It will be a different formula. I am away from my computer right now... I will look at it when I get back to my computer. However, show me a sample of how the converted value should look (I am interested in how the parts should be labeled).

Hello @ Rick,
It would look the same as that one except going from Meters to Inches to Ft and Inches
Then for MM's to inches and FT and Inches. but in the same formatting as the CM's you just solved was. Thanks

 

[Phuynh]

Convert from (decimal) centimeter to (literial text) feet-inches with round-off 1/16
Note: if you want round-off to different value such as 1/8 1/4, 1/2, 1..., just replace 1/16 with your round-off number.

Phh

=IF(NOT(ISNUMBER(A1)),"n/a",
 IF(OR(MROUND(A1/2.54,SIGN(A1)*1/16)>=12,MROUND(A1/2.54,SIGN(A1)*1/16)<=-12),
 ROUNDDOWN(MROUND(A1/2.54,SIGN(A1)*1/16)/12,0)&"'-"&TEXT(MOD(MROUND(ABS(A1/2.54),1/16),12),"0 #/##")&"""",
 TEXT(MROUND(A1/2.54,SIGN(A1)*1/16),"# #/##")&""""))

 

[Phuynh]

Hello @ Rick,
It would look the same as that one except going from Meters to Inches to Ft and Inches
Then for MM's to inches and FT and Inches. but in the same formatting as the CM's you just solved was. Thanks

Other formula variant, use lambda function if you know your conversion factor and round-off to the number of your choose

Lambda function for convert decimal value to imperial feet-inches fraction, text format [#'-# #/#"]

Parameters notes:
varN = Required, decimal number value to be converted (ex: A1, B10, C15, etc...)
cFact = Required, conversion factor value (1 = inch, 1/12 = feet, 25.4 = millimeter, 2.54 = centimeter, 0.0254 = meter, etc...)
rdOff = Required, rounded to the desired multiple (ex: 12, 1, 1/2, 1/4, 1/8, 1/16, 1/4096, etc...)

Ex:
Excel name: testfunc (or some meaningful name)
usage: testfunc(varN, cFact, rdOff)

=lambda(varN, cFact, rdOff,
 IF(NOT(ISNUMBER(varN)),"n/a",
 IF(OR(MROUND(varN/cFact,SIGN(varN)*cFact)>=12,MROUND(varN/cFact,SIGN(varN)*cFact)<=-12),
 ROUNDDOWN(MROUND(varN/cFact,SIGN(varN)*rdOff)/12,0)&"'-"&TEXT(MOD(MROUND(ABS(varN/cFact),rdOff),12),"0 #/####")&"""",
 TEXT(MROUND(varN/cFact,SIGN(varN)*rdOff),"# #/####")&""""))
)

 

[Phuynh]

Formula correction from previous post
Second IF statement should remove "cFact" and replace with "rdOff"

The formula should be:

=lambda(varN, cFact, rdOff,
 IF(NOT(ISNUMBER(varN)),"n/a",
 IF(OR(MROUND(varN/cFact,SIGN(varN)*rdOff)>=12,MROUND(varN/cFact,SIGN(varN)*rdOff)<=-12),
 ROUNDDOWN(MROUND(varN/cFact,SIGN(varN)*rdOff)/12,0)&"'-"&TEXT(MOD(MROUND(ABS(varN/cFact),rdOff),12),"0 #/####")&"""",
 TEXT(MROUND(varN/cFact,SIGN(varN)*rdOff),"# #/####")&""""))
)

My bad!, My apology for the error!

Phh

 

[Phuynh]

Hello Everyone,

At home, my Excel version not lambda capable like at work and I unable to test out, can you help?
To confirm that if I wrap the SUM function around "VarN" in the calculation for performing range computation does the formula still work?

Thanks,

Phh

=lambda(VarN, cFact, rdOff,
 IF(NOT(ISNUMBER(SUM(VarN))),"n/a",
 IF(OR(MROUND(SUM(VarN)/cFact,SIGN(SUM(VarN))*rdOff)>=12,MROUND(SUM(VarN)/cFact,SIGN(SUM(VarN))*rdOff)<=-12),
 ROUNDDOWN(MROUND(SUM(VarN)/cFact,SIGN(SUM(VarN))*rdOff)/12,0)&"'-"&TEXT(MOD(MROUND(ABS(SUM(VarN)/cFact),rdOff),12),"0 #/####")&"""",
 TEXT(MROUND(SUM(VarN)/cFact,SIGN(SUM(VarN))*rdOff),"# #/####")&""""))
)