“Weight”

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
578
Office Version
2019
Platform
Windows
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))
 

Some videos you may like

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.

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
578
Office Version
2019
Platform
Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,095,726
Messages
5,446,161
Members
405,387
Latest member
michmichmich2020

This Week's Hot Topics

Top