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.
|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)|
|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|