IF,AND,OR statements not working

[bhenlee]

Hi Experts,

I can't seem to get this formula working:

=IF(COUNTBLANK(H4:Q4)>0,"",IF(A4="E123",IF(OR(G4>1,U4<=5),"Disqualified",IF(A4<>"E123",IF(OR(F4="",H4<93%,G4>1,J4>0,K4>60,L4>1,M4>0,N4>0,O4>0,P4<95%,Q4<12),"Disqualified","Qualified")))))

What I'm trying to do is to make the statement unique for one individual and the same for the rest.

The criteria is different for one individual and the same for the rest.

Hope you could help.

[djapigo]

Nothing seems to be wrong with your formula...

I wonder though in your large OR statement... OR(F4="",H4<93%,G4>1,J4>0,etc.

Did you mean G4 or I4? Is there something in I4? If not, then the COUNTBLANK condition in the beginning of your formula will always be "".

Anyway, that's the only thing I can see from your formula...

Use the F9 technique... highlight any a small section of your formula... press F9 and it will calculate the items you highlight... you have to highlight a "formula" or cell within the formula for it to calculate... go down your formula until you find the error...

Good luck!

[bhenlee]

there's data in I4 but it is not included in the criteria and there will always be data in I4 so the countblank is not really an issue.

I just want a unique criteria for one individual and the same for the rest without having to actually come up with a separate formula for that one individual.

[djapigo]

OK, looking at this closer, I assume, E123 is the individual in question, no?

Is there a scenario where E123 is "Qualified"? Also, A4<>"E123" is redundant since the FALSE statement of A4="E123" is just that...

Will this work instead?

=IF(COUNTBLANK(H4:Q4)>0,"",IF(OR(AND(A4="E123",OR(G4>1,U4<=5)),OR(F4="",H4<93%,G4>1,J4>0,K4>60,L4>1,M4>0,N4>0,O4>0,P4<95%,Q4<12)),"Disqualified","Qualified"))

If this still does not work, can you attach a spreadsheet? Make sure to not include sensitive information. What results did you expect with your sample records?

[bhenlee]

here's how it works:

E123 has a different set of criteria than the others.. so basically his criteria is just this:

OR(G4>1,U4<=5)

and the rest of the group is:

OR(F4="",H4<93%,G4>1,J4>0,K4>60,L4>1,M4>0,N4>0,O4>0,P4<95%,Q4<12))

so if E123's H4 data is 89.99% that wouldn't matter because it's not part of her criteria so he should still be qualified assuming his G4<1 and U4>=5.

Thank you

[djapigo]

Try this...

=IF(COUNTBLANK(H4:Q4)>0,"",IF(A4="E123",IF(OR(G4>1,U4<=5),"Disqualified","Qualified"),IF(OR(F4="",H4<93%,G4>1,J4>0,K4>60,L4>1,M4>0,N4>0,O4>0,P4<95%,Q4<12),"Disqualified","Qualified")))

This breaks down like this...

If A4="E123" then
IF(OR(G4>1,U4<=5),"Disqualified","Qualified")
else
IF(OR(F4="",H4<93%,G4>1,J4>0,K4>60,L4>1,M4>0,N4>0,O4>0,P4<95%,Q4<12),"Disqualified","Qualified")

I hope this works for you...