Using unit of measurement in a formula?

Hi, I’m making a database of coffee beans. Roasters often sell in different units of measurement like grams, pounds, kilograms and ounces.

I have a column for weight (number) and another for unit of measurement (single select). I also have price. Is there a way I can use a formula to calculate price per pound without requiring extra work from contributors?

you can create your formula depending on the unit of measurement by nesting IF statements.
This is not the cleanest way but I guess that is the only one. Try something like this (pseudocode)

    {measure unit} = 'kg'}
    , price / 0.4536
    , IF(
        {measure unit} = 'lb'}
        , price / 1
        , IF(
          {mesure unit} = "gr"
          , price / 0.0004536
          , price / 0.0625

I think I did the right kg to lb conversion (1lb = 0.4536kg). The last case is the conversion between ounces an pounds (remaining option)

let me know if this works for you
*Xavier GS

1 Like

You can get to the same result using a SWITCH() formula as well.

{price} / SWITCH(
   {measure unit}
   'kg', 0.4536,
   'lb', 1,
   'gr', 0.0004536,
1 Like

I have never used the SWITCH operator.
Thank you Kamille for the great tip

It worked, thanks so much! I even figured out how to make it “price per espresso” (using 20g as an average dose) Airtable - Canadian Coffee Breakdown

1 Like

This topic was solved and automatically closed 15 days after the last reply. New replies are no longer allowed.