How can i get the correct result from my if statement-CodePudding

Hello members hope you are fine, am having an issue with my if function:

=IF(OR(K2="",M2="",O2=""),"",
    IF(OR(J2<=45,L2<=45,N2<=45),"O",
       IF(AND(J2<=35,L2<=35,N2<=35),"F",
          IF(AVERAGE(K2,M2,O2)<=2.5,"A",
             IF(AVERAGE(K2,M2,O2)<=3.5,"B",
                IF(AVERAGE(K2,M2,O2)<=4.5,"C",
                   IF(AVERAGE(K2,M2,O2)<=5.5,"D",
                      IF(AVERAGE(K2,M2,O2)<=6.5,"E",
                         IF(AVERAGE(K2,M2,O2)<=7.5,"F",
                            IF(AVERAGE(K2,M2,O2)<=9,"0"))))))))))

I want it to return O in-case J2 OR L2 OR N2 are less than 45 and return F if any two from(J2, L2 , N2) are less than 35 but it returns O only if all are less than 45 and if all them are less than 35 it gives F yet i want it to considered only two for it to return F

CodePudding user response：

I didn't test this, but I believe you just need to switch the second and third IF-cases:

=IF(OR(K2="",M2="",O2=""),"",
    IF(AND(J2<=35,L2<=35,N2<=35),"F",
       IF(OR(J2<=45,L2<=45,N2<=45),"O",
          IF(AVERAGE(K2,M2,O2)<=2.5,"A",
             IF(AVERAGE(K2,M2,O2)<=3.5,"B",
                IF(AVERAGE(K2,M2,O2)<=4.5,"C",
                   IF(AVERAGE(K2,M2,O2)<=5.5,"D",
                      IF(AVERAGE(K2,M2,O2)<=6.5,"E",
                         IF(AVERAGE(K2,M2,O2)<=7.5,"F",
                            IF(AVERAGE(K2,M2,O2)<=9,"0"))))))))))

At least two of J2, L2 and N2 need to be smaller than 35. The only way to write this:

(J2<=35 AND L2<=35) OR
(J2<=35 AND N2<=35) OR
(L2<=35 AND N2<=35)

In Excel:

OR(AND(J2<=35, L2<=35),
   AND(J2<=35, N2<=35),
   AND(L2<=35, N2<=35))

So, your formula becomes:

=IF(OR(K2="",M2="",O2=""),"",
    IF(OR(AND(J2<=35, L2<=35),AND(J2<=35, N2<=35),AND(L2<=35, N2<=35)),"F",
       IF(OR(J2<=45,L2<=45,N2<=45),"O",
          IF(AVERAGE(K2,M2,O2)<=2.5,"A",
             IF(AVERAGE(K2,M2,O2)<=3.5,"B",
                IF(AVERAGE(K2,M2,O2)<=4.5,"C",
                   IF(AVERAGE(K2,M2,O2)<=5.5,"D",
                      IF(AVERAGE(K2,M2,O2)<=6.5,"E",
                         IF(AVERAGE(K2,M2,O2)<=7.5,"F",
                            IF(AVERAGE(K2,M2,O2)<=9,"0"))))))))))

(Again, not tested, just done some copy/paste)