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

S

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.
 
N

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.
|
 
D

daddylonglegs

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))
 
S

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?
 
G

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Lookup Formula 3
Lookup? 4
Random letter and number generator 4
IF and OR 4
Totaling Cells with Like Information 3
Lookup query 4
Conditional Formatting 2
Conditional Formatting Text!! 7

Top