Using unit of measurement in a formula?

[Air_Hockey]

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?

[Xavier_G_Sabate]

Hi,
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)

 IF(
    {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

[Kamille_Parks]

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,
   0.0625
)

[Xavier_G_Sabate]

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

[Air_Hockey]

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