Skip to main content

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

Hi Zodeci,



Is the Qty field set as a number with precision 1.00 ? If not then try to do so

Hi Zodeci,



Is the Qty field set as a number with precision 1.00 ? If not then try to do so


Yes, it is. Any ideas?

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.

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.


i can’t achieve this with ROUND? I don’t understand why some calculations end in .00 and others .XXXXXXXXXXXXX

i can’t achieve this with ROUND? I don’t understand why some calculations end in .00 and others .XXXXXXXXXXXXX


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.

i can’t achieve this with ROUND? I don’t understand why some calculations end in .00 and others .XXXXXXXXXXXXX


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.

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.




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.

Reply


Terms & Conditions