Nested IF, AND, OR/AND

ncanales

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

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.