Re: Your result type is not a number or a date. Formatting options are currently only available if your result type is a number or a date

1284 0
Showing results for 
Search instead for 
Did you mean: 
4 - Data Explorer
4 - Data Explorer

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

7 Replies 7
5 - Automation Enthusiast
5 - Automation Enthusiast

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.

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.

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.