IF(AND function with Blanks

[perkinsr1]

Hi,

Hope you can help me? I am using the below string and it is giving me most of the answer, however I want it to be blank if either R13 or S13 are blank.

=IF(AND(R13="Below",S13="Below"),"PASS",IF(AND(R13="Emerging",S13="Emerging"),"PASS",IF(AND(R13="Effective",S13="Effective"),"PASS",IF(AND(R13="Expert",S13="Expert"),"PASS","FAIL"))))


Thanks in advance

Rob

 

[joeu2004]

Ostensibly:

=IF(OR(R13="",S13=""), "",
IF(AND(R13="Below",S13="Below"),"PASS",IF(AND(R13="Emerging",S13="Emerging"),"PASS",
IF(AND(R13="Effective",S13="Effective"),"PASS",IF(AND(R13="Expert",S13="Expert"),"PASS","FAIL")))))

But I wonder if one of the following works for you:

=IF(OR(R13="",S13=""), "", IF(R13=S13, "PASS", "FAIL"))

or

=IF(OR(R13="",S13=""), "", IF(AND(R13=S13, OR(R13={"Below","Emerging","Effective","Expert"})), "PASS", "FAIL"))

Use the latter only if R13 (and S13) might have some other value that you do not want to allow.

 

[perkinsr1]

Ostensibly:

=IF(OR(R13="",S13=""), "",
IF(AND(R13="Below",S13="Below"),"PASS",IF(AND(R13="Emerging",S13="Emerging"),"PASS",
IF(AND(R13="Effective",S13="Effective"),"PASS",IF(AND(R13="Expert",S13="Expert"),"PASS","FAIL")))))

But I wonder if one of the following works for you:

=IF(OR(R13="",S13=""), "", IF(R13=S13, "PASS", "FAIL"))

or

=IF(OR(R13="",S13=""), "", IF(AND(R13=S13, OR(R13={"Below","Emerging","Effective","Expert"})), "PASS", "FAIL"))

Use the latter only if R13 (and S13) might have some other value that you do not want to allow.

Hi Joeu2004, the first one work perfect, thank you!!!