IF formula based on percent not returning int values

[Phil_Vallender]

Hello,

I’m trying to do a fairly basic calculation that is conditional on a percentage field - if the percent is above a threshold, return a calculated number, if below return 0. My formula works with anything except zero for value 2.

Here’s my formula:

IF(
  (Potential*100) >= 490,
    {Estimated Points}*Potential,
    0
)

Any help greatly appreciated.

[Vivid-Squid]

Hi @Phil_Vallender
What is the error that you are getting?

[Justin_Barrett]

Welcome to the community, @Phil_Vallender! :grinning_face_with_big_eyes:

Phil_Vallender:

My formula works with anything except zero for value 2.

Could you please clarify what you mean by this? Which value is “value 2”?

Going off of your subject line—re: the formula not returning an integer—one thing to keep in mind is that you can control the display formatting of a formula that outputs a numeric value. To adjust this, open the formula field settings, then click on the “Formatting” tab. There you can choose whether the field displays a decimal value or an integer.

Keep in mind that this formatting control is for display purposes only. Internally the calculated value is likely stored as a decimal, and will return as such to any other formula that asks for it. If you want other formulas to treat the output from this one as a whole number, wrap your current formula in one of Airtable’s rounding functions.

[Phil_Vallender]

Hey! Sorry, by ‘value 2’ I mean the return value from the IF state when false.

When false, I want the IF statement to simply return 0 (as a number).

[Phil_Vallender]

Here’s my formula:

The red squiggle error says missing parenthesis (but adding doesnt seem to fix).

Basically the formula seems to work when both return Value 1 and Value 2 are either text or other fields, but as soon as I enter 0, all results go to 0 too!

Thanks.

[Phil_Vallender]

Phil_Vallender:

Hey! Sorry, by ‘value 2’ I mean the return value from the IF state when false.

When false, I want the IF statement to simply return 0 (as a number).

Hey! Sorry, by ‘value 2’ I mean the return value from the IF state when false.

When false, I want the IF statement to simply return 0 (as a number).

[Justin_Barrett]

Phil_Vallender:

Hey! Sorry, by ‘value 2’ I mean the return value from the IF state when false.

When false, I want the IF statement to simply return 0 (as a number).

I saw your reply the first time. No need to repeat it. :slightly_smiling_face:

That’s very strange. The formula syntax looks fine. I created that same formula myself and didn’t get any error indicator.

The first thing to try is to shut down and restart your browser (or the standalone app if that’s what you’re using). Sometimes a browser that’s left open too long can cause unexpected things to happen with Airtable. I’ve seen it firsthand on more than one occasion, and a restart of the browser is the only thing that will cure some ailments.

If that doesn’t fix it, try retyping the formula from scratch in a new formula field. If it gives the same error there, definitely report it to Airtable support.

I wish that I had more to suggest, but this is truly one of the most bizarre errors I’ve seen.

[Phil_Vallender]

Yeah, sorry about that - first time didnt show as being a reply to your post, so I thought I’d better retry.

Will look again and see if the formula can be fixed based on your suggestions.

Thanks.

[Justin_Barrett]

Phil_Vallender:

Yeah, sorry about that - first time didnt show as being a reply to your post, so I thought I’d better retry.

I appreciate the thought, but I saw it even though it wasn’t a direct reply to my comment. My participation in the thread meant that I’d receive certain notifications anyway.

Looking forward to seeing how this turns out!