“Weight”

[Marwan69]

Hi,

i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my formula also convers ml & g which I don’t want to be touched. For example: Any smaller unit/weight i want my formula to ignore. How can I incorporate this in my formula?

Many thanks.

 

[KRice]

So one option is to use a lookup table that tabulates the combinations of unit bases that are used in items purchased, as well as the conversion factor needed to convert to your preferred units. That table is built once and moved aside somewhere. You then populate the shaded cells and the formulas do the lookups to pull in the appropriate conversion factor...and the preferred units are automatically inserted to clarify what they are.

unitconv.xlsx
	A	B	C	D	E	F	G	H	I	J
1		Coffee Beans	Monin Blue Ocean syrup	Monin Pomergranate syrup	Monin Vanilla syrup			Item unit basis (as sold) (A)	Conv factor (A/cf=B)	Preferred Unit (B)
2	Unit cost	65	57	57	57			kg	1000	g
3	Item unit basis qty (as sold)	1	1	700	700			L	1000	mL
4	Item unit basis (as sold) (A)	kg	L	mL	mL			mL	1	mL
5	Cost per preferred unit basis	0.065	0.057	0.081428571	0.081428571
6	Preferred unit basis (B)	g	mL	mL	mL
Sheet1

Cell Formulas
Range	Formula
B5:E5	B5	=B2/B3/INDEX($I$2:$I$4,MATCH(B4,$H$2:$H$4,0))
B6:E6	B6	=INDEX($J$2:$J$4,MATCH(B4,$H$2:$H$4,0))

 

[Marwan69]

Thanks KRice for you effort. I’ll try and let you know.
Many thanks ?

 

[KRice]

You're welcome...glad to help.

 

[Marwan69]

Worked perfectly!
Thanks mate

 

[KRice]

Excellent...glad to help! Just add any other unit conversions to the lookup table and adjust the lookup range in the formula to handle anything new that comes along. Thanks for letting me know.

 

[Marwan69]

You really made my life a lot easier than it was!

 

[mikerickson]

Have you looked at the CONVERT function? One of its arguments is a string representation of the units.