Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Nov 10, 2021 07:09 AM
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?
Nov 10, 2021 09:10 AM
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
Nov 10, 2021 09:16 AM
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
)
Nov 10, 2021 11:56 PM
I have never used the SWITCH operator.
Thank you Kamille for the great tip
Nov 11, 2021 05:27 AM
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