IF limit reached... what function would be better?

[simonlime]

How would you solve this?

Based on some simple calculations, cell H11 has a value between 0 and
70. I would like I11 to show a text output depending on the value of
H11... For example a score of 55 would end up being 5b and a score of
27 would be 4c.

The following formula works
=IF(H11>=57,"5a",IF(H11>=54,"5b",IF(H11>=50,"5c",IF(H11>=42,"4a",IF(H11>=34,"4b",IF(H11>=26,"4c",IF(H11>=21,"3a",IF(H11>=17,"3b","<3b"))))))))

....but I know that I cannot expand it with any more IFs. I would like
to be able to return a blank cell if H11 was blank and have more
options if necessary in the future.

Any help really appreciated.

 

[Niek Otten]

Use a table and VLOOKUP instead.
Here's a tutorial:

http://www.contextures.com/xlFunctions02.html

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| How would you solve this?
|
| Based on some simple calculations, cell H11 has a value between 0 and
| 70. I would like I11 to show a text output depending on the value of
| H11... For example a score of 55 would end up being 5b and a score of
| 27 would be 4c.
|
| The following formula works
|
=IF(H11>=57,"5a",IF(H11>=54,"5b",IF(H11>=50,"5c",IF(H11>=42,"4a",IF(H11>=34,"4b",IF(H11>=26,"4c",IF(H11>=21,"3a",IF(H11>=17,"3b","<3b"))))))))
|
| ...but I know that I cannot expand it with any more IFs. I would like
| to be able to return a blank cell if H11 was blank and have more
| options if necessary in the future.
|
| Any help really appreciated.
|

 

[Guest]

Try LOOKUP instead, e.g.

=IF(H11="","",LOOKUP(H11,{0,17,21,26,34,42,50,54,57;"<3b","3b","3a","4c","4b","4a","5c","5b","5a"}))

although it's probably better to convert the above matrix to a table, i.e.
put, 0,17,21 etc. in A1 down and the corresponding grades in B1 down and use
the simpler

=IF(H11="","",LOOKUP(H11,A1:B9))

 

[simonlime]

Excellent thank you - I will give that a go...

Will that formula allow scored between the numbers to give the correct
answer, e.g. would a result of 20 give a 3b?

 

[Gord Dibben]

Yes, 20 will return 3b because it is greater than 17 but less than 21

Why don't you try it to see what happens?

To test it, stick formula in I11, put 1 in H11 and 2 in H12

Drag/copy these down to H80 or somewhere.

Now drag the formula down to see if the returns are what you want.


Gord Dibben MS Excel MVP