Upcoming database upgrades. Airtable functionality will be reduced for ~15 minutes at 06:00 UTC on Feb. 4 / 10:00 pm PT on Feb. 3. Learn more here

Using unit of measurement in a formula?

Topic Labels: Formulas
501 4
Showing results for 
Search instead for 
Did you mean: 

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?

4 Replies 4

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

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,

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