Excel 2007 : Nested IF(AND(.. Statement

[BingoTed]

Hi

I hav estruggled for a while with this one and have finally got the output without the IF statement going True on me too early but want to know if there is a better way of doing this.

I want to set up a percentage input field that is free entry (put any value between 0 and 100) and have another field output a status based upon the value. I have put the basic table below;

0 = Not started
1 ~ 9 = Initiating
10 ~ 69 = Implementing
70 ~ 94 = Testing
95 ~ 99 = Closing
100 = Closed

This If AND statement Works but I'm unsure if this is the best way of going about it.

=IF(G21=0,"Not Started",IF(AND(G21>=1,G21<=9),"Initiating",IF(AND(G21>=10,G21<=69),"Implementing",IF(AND(G21>=70,G21<=94),"Testing",IF(AND(G21>=95,G21<=99),"Closing",IF(G21=100,"Closed","No Value"))))))

Any help or guidance would be greatly received.

Thanks

[NBVC]

Try

=if(G21="","No Value",Lookup(G21,{0,1,10,70,95,100}, {"Not Started","Initiating","Implementing","Testing","Closing","Closed"}))

[BingoTed]

Thanks, tried it and it works perfectly. I'm going to play around with the lookup function now i've seen how effectively you can combine it here.

Thanks again