Skip to main content

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!

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.

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.

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

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:


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

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:


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

Reply


Terms & Conditions