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
 10.9K Get Help
 65 Global Discussions
 69 Industry Talk
 385 Announcements
 3.6K Ideas & Feature Requests
 56 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!