Nested IF Statement

[Rose_Wilson]

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!

[augmented]

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.

[Rose_Wilson]

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.

[augmented]

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

[Rose_Wilson]

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:

[augmented · Accepted solution]

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.

[Rose_Wilson]

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!