IF/And for compliance percentage

[Drexl88]

Hi,

I am using the below formula to return text base on the percentage in a cell H12; however, whatever the percentage in H12 the cell with the formula returns "Unsatisfactory (1)"

=IF(AND(H11>=0,H11<=40),"Unsatisfactory (1)",IF(AND(H11>=40.01,H11<=59.9),"Weak (2)",IF(AND(H11>=60,H11<=69.9),"Adequate (3)",IF(AND(H11>=70,H11<=84.9),"Good (4)",IF(AND(H11>=85,H11<=95.9),"Very Good (5)",IF(AND(H11>=96,H11<=100),"Excellent (6)"," ""Zero"))))))

Please help.

Thanks

Simon

 

[arthurbr]

How is you percentage expressed in H11? Like 40% or 0.4 or 40 ? Depending on that you will have to adapt the value in H11 ( or the values in the formula)
After that, you can also shorten your formula to something like

=LOOKUP(A11,{0;40;60;70;85;96;100},{"Unsatisfactory (1)";"Weak (2)";"Adequate (3)";"Good (4)";"Very Good (5)";"Very Good (5)"})

(I don't see where "zero" would fit in)