Combine AND, OR in an IF Statement
 

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

IF(AND(A2="Compliant",A3="Compliant")OR(A2="Compli ant",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

Combine AND, OR in an IF Statement
 

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

On Feb 8, 4:55*pm, FrankTimJr
wrote:
Is it possible to perform the following calculation? *(I hope it's not too
confusing).

IF(AND(A2="Compliant",A3="Compliant")OR(A2="Compli ant",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

Combine AND, OR in an IF Statement
 

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" wrote in message
...
Is it possible to perform the following calculation? (I hope it's not too
confusing).

IF(AND(A2="Compliant",A3="Compliant")OR(A2="Compli ant",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

Combine AND, OR in an IF Statement
 

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(A N2="Non Compliant",AO2="Non
Compliant"),AND(AN2="Non
Compliant",AO2="Compliant"),AND(AN2="Compliant",AO 2="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" wrote:

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" wrote in message
...
Is it possible to perform the following calculation? (I hope it's not too
confusing).

IF(AND(A2="Compliant",A3="Compliant")OR(A2="Compli ant",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

.

Combine AND, OR in an IF Statement
 

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

"FrankTimJr" wrote in message
...
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(A N2="Non
Compliant",AO2="Non
Compliant"),AND(AN2="Non
Compliant",AO2="Compliant"),AND(AN2="Compliant",AO 2="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" wrote:

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" wrote in message
...
Is it possible to perform the following calculation? (I hope it's not
too
confusing).

IF(AND(A2="Compliant",A3="Compliant")OR(A2="Compli ant",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

.