overcome from the restriction of number of IF statements

[J_J]

Hi all,
I have two formulas in two adjacent cells. One beeing:
=IF(AND(AA2>89;AA2<101);"AA";IF(AND(AA2>84;AA2<90);"BA";IF(AND(AA2>79;AA2<85);"BB";IF(AND(AA2>74;AA2<80);"CB";"-"))))
and the other
=IF(AND(AA2>69;AA2<75);"CC";IF(AND(AA2>64;AA2<70);"DC";IF(AND(AA2>59;AA2<65);"DD";IF(AND(AA2>49;AA2<60);"FD";IF(AND(AA2>0;AA2<50);"FF";"-")))))
I was intending two use one single cell for the purpose but failed because
of the restriction of the number of IF conditions within a single cell. Is
there a cure for this problem?. Can I combine the two cell conditions into
one cell with a combined formula?
Thanks
J_J

 

[RobinMelbourne]

I would consider replacing the IF's with a combination of INDEX and
MATCH, for example:

=INDEX($B$1:$B$10,MATCH($AA$2+1,$A$1:$A$10,-1))

this formula requires you to have a look up table as follows in
A1:B10....

100000 -
101 AA
90 BA
85 BB
80 CB
75 CC
70 DC
65 DD
60 FD
50 FF

You should get exactly the same result as per your IF statements.
Note that for the MATCH to work correctly the values must be in
descending order - look at the XL help file for a description of the
arguments.

Hope that helps, good luck.

 

[J_J]

Thanks RobinMelbourne,
Have a few additional questions if I may.
Before trying out your formula, is there a small mistake in the pharanthesis
of your MATCH content?
Does your formula assumes that I have my numbers in between B1:B10 to
compare with the appropriate letters?
Besides I'd appreciate to have the look up table in another sheet (Sheet2
for example). How that affects the formula?.
Regards
J_J

 

[RobinMelbourne]

JJ,

Checked my posting, and I don't think there is a mistake in the
parentheses - it should work fine as it is.
The formula works by MATCHing the input number (cell AA2 in your
example) in the first column (A1:A10), and then INDEXing into the
second column (B1:B10) by the result of the MATCH. Therefore the
corresponding letters must be in the same row as the number you are
matching against.

The third argument in the MATCH is set to -1; this finds the smallest
value that is greater than or equal to (hence the +1 in my formula) the
look up value.
The look up table can be any sheet you like (you could also hide the
sheet if you want!). If you put the look up table into Sheet2 the
formula would be:

=INDEX(Sheet2!$B$1:$B$10,MATCH($AA$2+1,Sheet2!$A$1:$A$10,-1))

Robin

 

[J_J]

Hi RobinMelbourne,
Sorry but the mistake was mine. You are correct. Your formula works fine...
J_J