excel if then formula not working in 2003

[cnu nagula]

Dear Friends,

i have a worksheet in 2007, the following formula is created to class report purpose and it is working properly in 2007, the sheet saved as to 2003 and the formula is not working, please help for it.

=IF(E9<9,"Fail",IF(F9<9,"Fail",IF(G9<9,"Fail",IF(H9<9,"Fail",IF(I9<9,"Fail",IF(E9="A","Fail",IF(F9="A","Fail",IF(G9="A","Fail",IF(H9="A","Fail",IF(I9="A","Fail",IF(K9>=90,"A+",IF(K9>=80,"A",IF(K9>=70,"B+",IF(K9>=60,"B",IF(K9>=40,"C","D")))))))))))))))

 

[T. Valko]

Dear Friends,

i have a worksheet in 2007, the following formula is created to class report purpose and it is working properly in 2007, the sheet saved as to 2003 and the formula is not working, please help for it.

=IF(E9<9,"Fail",IF(F9<9,"Fail",IF(G9<9,"Fail",IF(H9<9,"Fail",IF(I9<9,"Fail",IF(E9="A","Fail",IF(F9="A","Fail",IF(G9="A","Fail",IF(H9="A","Fail",IF(I9="A","Fail",IF(K9>=90,"A+",IF(K9>=80,"A",IF(K9>=70,"B+",IF(K9>=60,"B",IF(K9>=40,"C","D")))))))))))))))

One way...

=IF(SUM(COUNTIF(E9:I9,{"<9","A"})),"Fail",IF(K9>=90,"A+",IF(K9>=80,"A",IF(K9>=70,"B+",IF(K9>=60,"B",IF(K9>=40,"C","D"))))))

 

[VoG]

Try

=IF(OR(E9<9,F9<9,G9<9,H9<9,I9<9),"Fail",IF(OR(E9="A",F9="A",G9="A",H9="A",I9="A"),"Fail",LOOKUP(K9,{0,40,60,70,80,90},{"D","C","B","B+","A","A+"})))

 

[CWatts]

You can only have 7 nested IF statements which is why it's not working.

You can merge the first several with an OR statement:

=IF(OR(E9<9,F9<9,G9<9,H9<9,I9<9),"Fail",TheRestOfYourTests)

which will test those without the IF, but if either of those are true, then it will kick out the fail but if not, then it will perform the rest of your testing.

 

[cnu nagula]

Thank you friends, its working

 

[T. Valko]

One way...

=IF(SUM(COUNTIF(E9:I9,{"<9","A"})),"Fail",IF(K9>=90,"A+",IF(K9>=80,"A",IF(K9>=70,"B+",IF(K9>=60,"B",IF(K9>=40,"C","D"))))))

Try

=IF(OR(E9<9,F9<9,G9<9,H9<9,I9<9),"Fail",IF(OR(E9="A",F9="A",G9="A",H9="A",I9="A"),"Fail",LOOKUP(K9,{0,40,60,70,80,90},{"D","C","B","B+","A","A+"})))

Thank you friends, its working

Combining the best of both worlds you end up with:

=IF(SUM(COUNTIF(E9:I9,{"<9","A"})),"Fail",LOOKUP(K9,{0,40,60,70,80,90},{"D","C","B","B+","A","A+"}))