Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
overcome from the restriction of number of IF statements
Hi all,
I have two formulas in two adjacent cells. One beeing: =IF(AND(AA289;AA2<101);"AA";IF(AND(AA284;AA2<90) ;"BA";IF(AND(AA279;AA2<85);"BB";IF(AND(AA274;AA2 <80);"CB";"-")))) and the other =IF(AND(AA269;AA2<75);"CC";IF(AND(AA264;AA2<70); "DC";IF(AND(AA259;AA2<65);"DD";IF(AND(AA249;AA2< 60);"FD";IF(AND(AA20;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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
overcome from the restriction of number of IF statements
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
overcome from the restriction of number of IF statements
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" wrote in message oups.com... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
overcome from the restriction of number of IF statements
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
overcome from the restriction of number of IF statements
Hi RobinMelbourne,
Sorry but the mistake was mine. You are correct. Your formula works fine... J_J "RobinMelbourne" wrote in message ups.com... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Overcome LOOKUP limits and NOW() | Excel Worksheet Functions | |||
How to overcome LOOKUP function problems? | Excel Worksheet Functions | |||
how do I overcome this combobox 'bug'? | Excel Programming | |||
Pivot Table - Overcome col. limit? | Excel Programming |