Help

Upcoming database upgrades. Airtable functionality will be reduced for ~15 minutes at 06:00 UTC on Feb. 4 / 10:00 pm PT on Feb. 3. Learn more here

If statement true if plus or minus value

Topic Labels: Formulas
Solved
Jump to Solution
321 6
cancel
Showing results for 
Search instead for 
Did you mean: 

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?

Thanks in advance!

1 Solution

Accepted Solutions

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.

See Solution in Thread

6 Replies 6

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.

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.

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))

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:
image

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

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.

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!