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

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",I F(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.

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

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

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

wrote in message ups.com...
| 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",I F(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.
|

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

Try LOOKUP instead, e.g.

=IF(H11="","",LOOKUP(H11,{0,17,21,26,34,42,50,54,5 7;"<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))



" wrote:

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",I F(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.

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

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?

daddylonglegs wrote:
Try LOOKUP instead, e.g.

=IF(H11="","",LOOKUP(H11,{0,17,21,26,34,42,50,54,5 7;"<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))



" wrote:

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",I F(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.

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

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

On 16 Dec 2006 00:50:13 -0800, wrote:

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?

daddylonglegs wrote:
Try LOOKUP instead, e.g.

=IF(H11="","",LOOKUP(H11,{0,17,21,26,34,42,50,54,5 7;"<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))



" wrote:

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",I F(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.