Ytdyklly

I have created following Formula, but it does not works:

=IF(AND(G2<41,L2<41),4,IF(AND(G2>40,G2<81),AND(L2>40,L2<81),5,IF(AND(G2>80,L2>80),6,IF(AND(G2>41,G2>81),AND(L2>80),7,IF(AND(G2<41,L2>40),AND(L2<81),8,IF(AND(G2<41,L2>80),9,IF(AND(G2>80,L2>40),AND(L2<81),3,IF(AND(G2>40,G2<81),AND(L2<41),2,IF(G2>81,L2<41),1,0)

Can anyone help me to improve the formula?

As AND is a binary operator requiring at least two argument to test that both are true, there four constructs having single conditions are wrong:

AND(L2>80), AND(L2<81), AND(L2<81), AND(L2<41)

in constructs like

AND(G2>41,G2>81)

that will hold true only if G2>81, so the first part, G2>41, is unnecessary, unless it should be AND(G2>41,G2<81)

for wrong constructs like

IF(AND(G2>41,G2<81),AND(L2>80)

You should use

IF( AND( AND(G2>41,G2<81),L2>80))

That will first test that G2 is >41 as well as <81, and if that holds it will also check whether L2 is also >80

If you take the single-digit numbers as intended for "result" for true tests, and then format the expression (formula) like this:

=IF( 

  AND(G2<41,L2<41),4,

  # else

    IF(AND(G2>40,G2<81),AND(L2>40,L2<81),5,

    # else

      IF(AND(G2>80,L2>80),6,

      # else

        IF(AND(G2>41,G2>81),AND(L2>80),7,

        #else 

          IF(AND(G2<41,L2>40),AND(L2<81),8,

          # else

            IF(AND(G2<41,L2>80),9,

            #else

              IF(AND(G2>80,L2>40),AND(L2<81),3,

              #else 

                IF(AND(G2>40,G2<81),AND(L2<41),2,

                # else 

                  IF(G2>81,L2<41),1,

                  # else

                  0)

... then it should be quite clear where the problem(s) exist.

Note that IF() takes ONE expression and tries whether it is TRUE or FALSE, returning the corresponding one of the two remaining arguments.

I'd recommend expressions/formulas to be kept as short as possible - it is very easy to end up with "a mess" otherwise - example here.

This is very similar to the situation where "smart code" is used for programming, i.e. where the remedy is: "KISS" (Keep it simple stupid).

Your Formula should written like this:

=IF(AND(G2<41,L2<41),4,IF(AND(G2>40,G2<81,L2>40,L2<81),5,IF(AND(G2>80,L2>80),6,IF(AND(G2>41,G2<=81,L2>80),7,IF(AND(G2<41,L2>40,L2<81),8,IF(AND(G2<41,L2>80),9,IF(AND(G2>80,L2>40,L2<81),3,IF(AND(G2>40,G2<81,L2<41),2,IF(AND(G2>81,L2<41),1,0)))))))))

Note:

• I've done little modification and instead of

IF(AND(G2>41,G2>81,L2>80),7

I've written like this,

IF(AND(G2>41,G2<=81,L2>80),7

because the previous logic And(G2>80, L2>80) for 6 was contradicting with IF(AND(G2>41,G2>81,L2>80),7