Complicated IF(AND & IF(OR Formula

Posted by Zif on October 20, 2000 2:42 AM

Hiya

I'm having trouble with an IF formula that I'm trying to write that has got some complicated conditions as follows:

If B2 is blank then "OK"
If B2 = "C" and ANY of C2, D2 or G2 are blank then "Error"
If B2 = "C" and ALL of C2, D2 and G2 are not blank then "OK"
If B2 = "E" and ANY of E2, F2 or H2 are blank then "Error"
If B2 = "E" and ALL of E2, F2 and H2 are not blank then "OK"

To summarise:
If B2 is blank, I need to flag as "OK"
If B2 contains "C", I need to flag "Error" if any of C2, D2 or G2 are blank. If they are all completed then flag "OK".
If B2 contains "E", I need to flag "Error if any of E2, F2 or H2 are blank. If they are all completed then flag "OK"

I've tried nesting IF(OR and IF(AND statements, but can't get the result I need. Any suggestions?

Zif

Posted by Zif on October 20, 2000 4:28 AM

Solution Found

I've found a way to do this using a simple IF statement. Solution is as follows:

=IF(IF(B2="",0,IF(B2="C",(COUNTBLANK(C2:D2)+COUNTBLANK(G2)),COUNTBLANK(E2:F2)))=0,"OK","Error")

Posted by Aladin.Akyurek on October 26, 2000 12:38 AM

Re: Solution Found

Posted by Aladin.Akyurek on October 26, 2000 12:41 AM

Re: Solution Found

Alternative with logical functions:

=IF(OR(AND(B2="C",NOT(OR(ISBLANK(C2),ISBLANK(D2),ISBLANK(G2)))),
AND(B2="E",NOT(OR(ISBLANK(E2),ISBLANK(F2),ISBLANK(G2)))),
ISBLANK(B2)),
"OK",
"Error")