# Re: If statement true if plus or minus value

Solved
569 5
cancel
Showing results for
Did you mean:
5 - Automation Enthusiast

Hi there,

I’m using airtable to compare an actual quantity with a goal quantity, then using an If, Then statement to tell me whether I’m at my goal, need to increase, or need to decrease my actual.

Here’s my current formula:

``````IF({% of total}={Goal % of total}, "OK",
IF({% of total}>{Goal % of total}, "Trim",
"Expand"))
``````

This is working fine, but what I would really like is if I were able to specify a range for values that are “close enough” to the goal. For example if {% of total} is plus or minus 0.5 of the goal, I want it to return OK. Anyone have any thoughts on how to do this?

1 Solution

Accepted Solutions
10 - Mercury

Oh duh! This is my fault. Wasn’t thinking about the % field, etc. Forget about FLOOR() for now, use…

`ROUND({% of total},2)`

Just need to remember 1.9% is 0.019. What an idiot I am.

6 Replies 6
10 - Mercury

Hi Rose. If you’re working with whole number %'s, then try using ROUND({% of total},0). It will give you 1% either way for your IF tests.

If your {Goal % of total} is a decimal number, then you would need a different technique to allow for +/- 0.5.

5 - Automation Enthusiast

Thank you!
When I try this:

``````IF(ROUND({% of total},0)={Goal % of total}, "OK",
IF({% of total}>{Goal % of total}, "Trim",
"Expand"))
``````

It still returns Expand for 1.9 and a goal of 2.

My Goal numbers are integers, actuals are decimals, maybe that’s what’s throwing it off.

10 - Mercury

That should work, but I’ve seen strange floating point number things in Airtable. Try putting FLOOR() around your ROUND() statement. It shouldn’t make a difference though since ROUND() should return an integer.

`FLOOR(ROUND({% of total},0))`

5 - Automation Enthusiast

Hmm, still getting the error with this:

``````IF(FLOOR(ROUND({% of total},0))={Goal % of total}, "OK",
IF({% of total}>{Goal % of total}, "Trim",
"Expand"))
``````

I tried to make another column that just rounded the number so that I could make the formula simpler, and I noticed something weird:

It doesn’t appear to like rounding my numbers :grinning_face_with_sweat:

10 - Mercury

Oh duh! This is my fault. Wasn’t thinking about the % field, etc. Forget about FLOOR() for now, use…

`ROUND({% of total},2)`

Just need to remember 1.9% is 0.019. What an idiot I am.

5 - Automation Enthusiast

Oh my gosh, OF COURSE! Thank you so much for seeing that. I would have been staring at the screen for hours more today. I knew I had to be missing something obvious!