Nested IF, AND, OR/AND
Hi all,
New user here. I have different monthly metrics to comply, some are greater than or lower than a value or percentage (i.e. Open tickets beyond due date is LOWER than 10%, Survey results are higher than 70%, Safety observations over 30).
I created a column [MEASUREMENT] stating "Higher than" or "Lower than", a column [ACTUAL], with the Actual Value/Score obtained in the month and the target [TARGET]... and then wrote the formula below
=IF(AND(Measurement2 = "Higher or Equal Than", ACTUAL2 >= Target2, OR(AND(Measurement2 = "Lower or Equal Than", ACTUAL2 <= Target2))), 1, 0)
Obviously something is wrong because although I get no errors, the result is always 0.
At last I created a rule... if SCORE is "0" then red, if "1" then Green"
Any help?? Pleeease... Thanks in advance for any "guru" willing to help
N
Comments

Original:
=IF(AND(Measurement2 = "Higher or Equal Than", ACTUAL2 >= Target2, OR(AND(Measurement2 = "Lower or Equal Than", ACTUAL2 <= Target2))), 1, 0)
This can be made easier to understand by stacking an if statement
=if(Measurement2 = "Higher or Equal Than",if(Actual2>=Target2,1,0),if(Measurement2 = "Lower or Equal Than",if(Actual2 <= Target2,1,0)
That said you can pursue this in the way that you approached originally (single if statement)
=if(or(and(Measurement2 = "Higher or Equal Than",Actual2 >= Target2),and(Measurement2 = "Lower or Equal Than", Actual2 <= Target2)),1,0)
Neither solution is more correct than the other, personally the first one I gave you is easier to understand I think. Potentially a typo or two in there, I didn't test these.

Thank you so much!! L@123... it worked like a charm!!
Much simpler.
Thanks again!!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.1K Get Help
 351 Global Discussions
 198 Industry Talk
 427 Announcements
 4.4K Ideas & Feature Requests
 133 Brandfolder
 127 Just for fun
 127 Community Job Board
 443 Show & Tell
 28 Member Spotlight
 1 SmartStories
 284 Events
 36 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!