Nested IF, AND, OR/AND

ncanales · 03/06/19

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

L_123 · 03/06/19

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.

ncanales · 03/07/19

Thank you so much!! L@123... it worked like a charm!!

Much simpler.

Thanks again!!

Nested IF, AND, OR/AND

Comments

Help Article Resources

Categories