Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Jan 22, 2021 11:58 PM
I have calculations going beyond the hundredth decimal and would like to be able to format the formula into currency to the .00:
IF({Unit} = “g”, {Price}/{Qty}*100 & “/100g”, IF({Unit} = “mL”, {Price}/{Qty}*100 & “/100mL”, IF({Unit} = “kg”, {Price}/{Qty} & “/kg”, IF({Unit} = “lb”, {Price}/{Qty} & “/lb”))))
Price Column is Currency format
thanks in advance
Jan 23, 2021 09:03 AM
Hi Zodeci,
Is the Qty field set as a number with precision 1.00 ? If not then try to do so
Jan 23, 2021 02:08 PM
Yes, it is. Any ideas?
Jan 23, 2021 03:11 PM
Your result type is a text string because you are concatenating the number with text for the units ("/100g", “/100mL”, “/kg”, or “/lb”). If you want Airtable to recognize your value as a number, it cannot include text based units.
Jan 23, 2021 08:50 PM
i can’t achieve this with ROUND? I don’t understand why some calculations end in .00 and others .XXXXXXXXXXXXX
Jan 24, 2021 09:36 AM
Yes, you can use ROUND()
. It will give you the number of decimal places you want. But it will not always have trailing zeros. If you want to always have two decimal places and grouping markers, you will have to use something like these pretty print routines.
My previous comment was about why you could not use Airtable’s formatting options on the formula result.
Jan 25, 2021 09:28 AM
It’s not a number field because of the words you’re adding at the end. /kg and /lb.
So, if 100 was the only thing in the field, that’s a number. But 100/lb is not a number, because of the letters.
If you really want the 100/lb, what I would personally do is remove the “/lb”, “/kg” and so on, then have another formula field that takes the output of the first field, in number form, and appends the /lb to that.
Jan 25, 2021 09:46 AM
While this is possible, the second formula field will not be able to benefit from the formatting (currency symbol, number of decimal places, commas, etc.) applied in the first formula field.