Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. | |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
Need some comments on my Utility_Move class module. | Excel Worksheet Functions | |||
How can I use Excel to solve an equation? | Excel Worksheet Functions | |||
Date & Time | New Users to Excel | |||
HOW CAN I GET OFFICE 2003 EXCEL BASIC TO NEST FUNCTIONS LIKE EXCE. | Excel Worksheet Functions |