Math problem converting from Imperial units to si units

[swemek]

Hey all!

I'm converting calculations from Imperial units to si units, but can't get it to match. I don't know if I did some conversion error or what is wrong. Or I'm blind.

The red should match up the green

I need your expertise.

Regards M

 

[rlv01]

I believe you misinterpreted the engineering units in cell B12 as inches, when they are really feet.
(ok, cell B11 in your data, B12 in mine since I added a header row).

URL_Maker.xlsm
	A	B	C	D	E	F
1	Imp Units	Value		SI Units	Value
2	fps	2851.04987		m/s	869
3	ft/s2	32.1740486		m/s2	9.80665001
4
5
6	inch	1.5		m	0.0381
7	inch	1.57480315		m	0.04
8	feet	0.1843586	=(0.425*B6+B7)/12	m	0.0561925	=(0.425*E6+E7)
9	s	0.21410353	=SQRT(8/B3)*SQRT(B8)	s	0.21410353	=SQRT(8/E3)*SQRT(E8)
10
11	feet	575.316892		m	175.356589
12	FEET!!!	8.50995592	=(B9^2)*B3*(1+2*B$11/B2/B9)*2	m	0.21615288	=(E9^2)*E3*(1+2*E$11/E2/E9)*2/12
13
14
15		0.00144992	=(B12+B6)/B11/12		0.00144992	=(E12+E6)/E11
16
17
Sheet4

Cell Formulas
Range	Formula
B8	B8	=(0.425*B6+B7)/12
B9,E9	B9	=SQRT(8/B3)*SQRT(B8)
E8	E8	=(0.425*E6+E7)
B12	B12	=(B9^2)*B3*(1+2*B$11/B2/B9)*2
E12	E12	=(E9^2)*E3*(1+2*E$11/E2/E9)*2/12
B15	B15	=(B12+B6)/B11/12
E15	E15	=(E12+E6)/E11

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E15	Expression	=$B$15=$E$15	text	NO
B15	Expression	=$B$15=$E$15	text	NO

 

[rlv01]

@ rlv01: Before diving any deeper, take a look at the formula in cell B11. It uses only ft, s, fps, and ft/s2, and it has no conversion into inches. So, the number in cell B11 is in feet, not in inches. Then, in cell B13, feet (B11) and inches (B5) are summed without any conversion, which is obviously wrong.

Ha! I didn't read this until I did my own analysis. It could have save me some time

 

[Tetra201]

@ rlv01: You are still making the same mistake in cell B15 -- summing feet (B12) and inches (B6) without any conversion. The formula in B15 is wrong, and it returns a wrong value. I've heard some space probes had been lost because of errors in converting of imperial units to metric and vice versa (doing thermodynamic calculations with BTU's and °R is even bigger fun).

 

[rlv01]

Adjusted per @Tetra201 input.

URL_Maker.xlsm
	A	B	C	D	E	F
1	Imp Units	Value		SI Units	Value
2	fps	2851.04987		m/s	869
3	ft/s2	32.1740486		m/s2	9.80665001
4
5
6	inch	1.5		m	0.0381
7	inch	1.57480315		m	0.04
8	feet	0.1843586	=(0.425*B6+B7)/12	m	0.0561925	=(0.425*E6+E7)
9	s	0.21410353	=SQRT(8/B3)*SQRT(B8)	s	0.21410353	=SQRT(8/E3)*SQRT(E8)
10
11	feet	575.316892		m	175.356589
12	FEET!!!	8.50995592	=(B9^2)*B3*(1+2*B$11/B2/B9)*2	m	2.59383456	=(E9^2)*E3*(1+2*E$11/E2/E9)*2
13
14
15		0.01500904	=(B12+B6/12)/B11		0.01500904	=(E12+E6)/E11
Sheet4

Cell Formulas
Range	Formula
B8	B8	=(0.425*B6+B7)/12
B9,E9	B9	=SQRT(8/B3)*SQRT(B8)
E8	E8	=(0.425*E6+E7)
B12,E12	B12	=(B9^2)*B3*(1+2*B$11/B2/B9)*2
B15	B15	=(B12+B6/12)/B11
E15	E15	=(E12+E6)/E11

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E15	Expression	=$B$15=$E$15	text	NO
B15	Expression	=$B$15=$E$15	text	NO

 

[rlv01]

@ rlv01: You are still making the same mistake in cell B15 -- summing feet (B12) and inches (B6) without any conversion. The formula in B15 is wrong, and it returns a wrong value. I've heard some space probes had been lost because of errors in converting of imperial units to metric and vice versa (doing thermodynamic calculations with BTU's and °R is even bigger fun).

Yeah, I was in a hurry. Oops. Good thing we're not building a space probe. .

 

[swemek]

Thanks so much everyone!
I think rlv01 has the right solution.

Again! Thanks so much for all the help!