# “Weight”

#### KRice

##### Well-known Member
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
ABCDEFGHIJ
1Coffee BeansMonin Blue Ocean syrupMonin Pomergranate syrup Monin Vanilla syrupItem unit basis (as sold) (A)Conv factor (A/cf=B)Preferred Unit (B)
2Unit cost65575757kg1000g
3Item unit basis qty (as sold)11700700L1000mL
4Item unit basis (as sold) (A)kgLmLmLmL1mL
5Cost per preferred unit basis0.0650.0570.0814285710.081428571
6Preferred unit basis (B)gmLmLmL
Sheet1
Cell Formulas
RangeFormula
B5:E5B5=B2/B3/INDEX(\$I\$2:\$I\$4,MATCH(B4,\$H\$2:\$H\$4,0))
B6:E6B6=INDEX(\$J\$2:\$J\$4,MATCH(B4,\$H\$2:\$H\$4,0))

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

#### Marwan69

##### New Member
Thanks KRice for you effort. I’ll try and let you know.
Many thanks

#### Marwan69

##### New Member
Worked perfectly!
Thanks mate

#### KRice

##### Well-known Member
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.