Nested IF AND statements

Question

Hi,

I am building a spreadsheet that averages score cards and then will print a statement next to the average column based on the one of four ranges of the average.

So the average cell is E3. I tried a nested IF AND statement as follows but excel wont accept it.

=IF(e3<4, "BELOW EXPECTATIONS", IF(AND(e3>0.4, e3<0.6), "Meets Expectations", IF(AND(e3>0.6, e3<0.8),"Above Expectations", IF(AND(e3>0.9, e3<1). "Exceeds Expectations"))))

What am I doing wrong?

Is there a better way to create a range of numbers to equal a statement versus using the AND (IE: IF e3 = 0.01 to 0.39 then "Below Expectations")?

Thanks,

Kevin

Answer 1

Hi,

Try this.

=IF(E3<>"",LOOKUP(E3,{0,0.4,0.6,0.9},{"Below Expectation","Meets Expectation","Above Expectation","Exceeds Expectation"}),"")

Answer 2

Hi,

In addition to what Mike and Hans already replied, you have gaps in your formula.

e3<4, e3>0.4, e3<0.6, e3>0.6, e3<0.8, e3>0.9, e3<1

What with the exact values 0.4, 0.6, 0.8 and between 0.8 & 0.9?

I guess Mike's formula will be one you need.

EDIT: ignore this post, Hans has covered it. Should read it all. Sorry Hans!

Answer 3

You have 4 instead of 0.4 in the first condition, and a . instead of a , before "Exceeds Expectations".

Moreover, the conditions don't cover all possible values of E3 between 0 and 1. Try this somewhat shorter formula:

=IF(E3<0.4, "BELOW EXPECTATIONS", IF(E3<0.6, "Meets Expectations", IF(E3<0.8, "Above Expectations",  "Exceeds Expectations")))