If And Or

K

k11ngy

Trying to nest a statement together but have problems

I need to:

If A1 and A2 = pass then Full award, Else ""

I also need to nest in for example

If A1 = pass or exempt and a2 = Pass, Exempt then full award else ""

Cant seem to fathom it

Thanks for help

Steve
 
M

Mike H

try

=IF(AND(OR(A1=UPPER("PASS"),A1=UPPER("Exempt")),OR(A2=UPPER("PASS"),A2=UPPER("EXEMPT"))),"Full Award","")

Mike
 
N

Nastech

is this what you want?

=IF(AND(OR(P1={"pass","exempt"}),P2="pass"),"full award","")
 
D

Dave Peterson

Another one:

=IF(OR(COUNTIF(A1:A2,"pass")=2,
AND(COUNTIF(A1:A2,"pass")=1,COUNTIF(A1:A2,"Exempt")=1)),"full","")
 
N

Nastech

found this info, not sure if way of shortening these examples up further.

another way of doing it.. something took while to get, to be able to add
many names to one spot while not using any more space:
create a defined name e.g. this formula: insert, name, defined, using P
as a made up name same as in this formula, enter: ={"pass";"exempt"}

=IF(AND(OR(A1={"pass","exempt"}),OR(A2={"pass","exempt"})),"full award","")
=IF(SUMPRODUCT(--ISNUMBER(FIND(P,A1:A2)))>0,"full award","")
=IF(AND(SUMPRODUCT(--ISNUMBER(FIND(P,A1)))>0,SUMPRODUCT(--ISNUMBER(FIND(P,A2)))>0),"full award","")

this will allow you to change values externally, or add many values to same
formula, without changing the formula.
 
N

Nastech

did a mix on above:

=IF(OR(COUNTIF(A1:A2,P)=2,AND(COUNTIF(A1:A2,P)=1,COUNTIF(A1:A2,E)=1)),F,"")
insert, name, define: P as: ={"pass"} E as: ={"exempt"} F as:
={"full award"}
 
N

Nastech

maybe shortest one yet: (using defined names)
=IF(AND(OR(A1=P,A1=E),OR(A2=A,A2=E)),F,"") or 1 char less:
=IF((OR(A1=P,A1=E))*(OR(A2=A,A2=E)),F,"")
 
N

Nastech

slight correction:

=IF(AND(OR(A1=P,A1=E),OR(A2=P,A2=E)),F,"")
=IF((OR(A1=P,A1=E))*(OR(A2=P,A2=E)),F,"")
 
Top