Combine AND, OR in an IF Statement

[FrankTimJr]

Is it possible to perform the following calculation? (I hope it's not too
confusing).

IF(AND(A2="Compliant",A3="Compliant")OR(A2="Compliant",A3="Not
Counted"),"Compliant","")

I have 9 seperate conditions but only three possible outcomes and you can
only nest up to 7. The conditions are the following:

A2, A3 (outcome on the three are all Compliant)
Compliant, Compliant
Compliant, Not Counted
Not Counted, Compliant

A2, A3 (outcome on all five are all Non Compliant)
Non Compliant, Non Compliant
Non Compliant, Compliant
Compliant, Non Compliant
Non Compliant, Not Counted
Not Counted, Non Compliant

A2, A3 (this is the only that equals Not Counted)
Not Counted, Not Counted

Thanks for the help.
Frank

 

[Pete_UK]

Try this:

=IF(AND(A2="Not Counted",A3="Not Counted"),"Not Counted",IF(OR(A2="Non
Compliant",A3="Non Compliant"),"Non Compliant","Compliant"))

Hope this helps.

Pete

 

[Fred Smith]

Try this:
=if(and(a2="not counted",a3="not counted"),"Not
Counted",if(and(or(a2="compliant",a2="not
counted"),or(a3="compliant",a3="not counted")),"Compliant","Non Compliant"))

Regards,
Fred

 

[FrankTimJr]

I got it.

I had to reverse it to OR AND rather than AND OR.

=IF(AND(AN2="Not Counted",AO2="Not Counted"),"Not
Counted",IF(OR(AND(AN2="Compliant",AO2="Compliant"),AND(AN2="Compliant",AO2="Not
Counted"),AND(AN2="Not
Counted",AO2="Compliant")),"Compliant",IF(OR(AND(AN2="Non Compliant",AO2="Non
Compliant"),AND(AN2="Non
Compliant",AO2="Compliant"),AND(AN2="Compliant",AO2="Non
Compliant"),AND(AN2="Non Compliant",AO2="Not Counted"),AND(AN2="Not
Counted",AO2="Non Compliant")),"Non Compliant","Next")))

In theory, the final "FALSE" condition should never happen and in my data it
doesn't.

Thank you!
Frank

 

[Fred Smith]

I'm glad it works for you. You might want to change "Next" to "Error" if is
should never happen. That's more likely to spur someone to action to look
for an error in the data.

Regards,
Fred