If And Or

[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

 

[Mike H]

try

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

Mike

 

[Nastech]

is this what you want?

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

 

[Dave Peterson]

Another one:

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

 

[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.

 

[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"}

 

[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,"")

 

[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,"")