Home |
Search |
Today's Posts |
|
#1
|
|||
|
|||
Get Past the 7 Nested IF limit
I need to implement a more than 7 IF nested Statements in a Master Excel sheet I am creating. It is a Tolerance Table that I need to put several times across the sheet and across the book. I know it can be done in VBA but I am not an expert in VBA.
Of course I tried to write the whole formula in EXCEL although I know it was not going to be accepted. Average % intervals________Tolerance 99 and 2_____________________ 2 97-98 and 3-4_________________3 94-96 and 5-7_________________4 91-93 and 8-10________________5 87-90 and 11-14_______________6 82-86 and 15-19_______________7 76-81 and 20-25_______________8 70-75 and 26-31_______________9 60-69 and 32-41______________10 51-59 and 42-50______________11 =IF(CELL=2,"2",IF(CELL=4,"3",IF(CELL=7,"4",IF(CELL =10,"5",IF(CELL=14,"6",IF(CELL=19,"7",IF(CELL=25," 8",IF(CELL=31,"9",IF(CELL=41,"10",IF(CELL=59,"11", IF(CELL=69,"10",IF(CELL=75,"9",IF(CELL=81,"8",IF(C ELL=86,"7",IF(CELL=90,"6",IF(CELL=93,"5",IF(CELL=9 6,"4",IF(CELL=98,"3",IF(CELL=99,"2"))))))))))))))) )))) Last edited by Palencia1978 : September 28th 05 at 08:03 PM |
#2
|
|||
|
|||
If you haven't already, you may want to check out the VLOOKUP function in the
help files. If the choices you listed are the only ones you need, this should do what you want: =VLOOKUP(B12,{2,2;4,3;7,4;10,5;14,6;19,7;25,8;31,9 ;41,10;59,11;69,10;75,9;81,8;86,7;90,6;93,5;96,4;9 8,3;99,2},2,0) -- tj "Palencia1978" wrote: I need to implement a more than 7 IF nested Statements in a Master Excel sheet I am creating. It is a Tolerance Table that I need to put several times across the sheet and across the book. I know it can be done in VBA but I am not an expert in VBA. Of course I tried to write the whole formula in EXCEL although I know it was not going to be accepted. Average % intervals________Tolerance 99 and 2_____________________ 2 97-98 and 3-4_________________3 94-96 and 5-7_________________4 91-93 and 8-10________________5 87-90 and 11-14_______________6 82-86 and 15-19_______________7 76-81 and 20-25_______________8 70-75 and 26-31_______________9 60-69 and 32-41______________10 51-59 and 42-50______________11 =IF(CELL=2,"2",IF(CELL=4,"3",IF(CELL=7,"4",IF(CELL =10,"5",IF(CELL=14,"6",IF(CELL=19,"7",IF(CELL=25," 8",IF(CELL=31,"9",IF(CELL=41,"10",IF(CELL=59,"11", IF(CELL=69,"10",IF(CELL=75,"9",IF(CELL=81,"8",IF(C ELL=86,"7",IF(CELL=90,"6",IF(CELL=93,"5",IF(CELL=9 6,"4",IF(CELL=98,"3",IF(CELL=99,"2"))))))))))))))) )))) -- Palencia1978 |
#3
|
|||
|
|||
In this particular case, the return values for the formula are nicely
arranged in numerical sequence, so you can use the MATCH function: =MATCH(CELL,{4,7,10,14,19,25,31,41,50},0)+1 "Palencia1978" wrote: I need to implement a more than 7 IF nested Statements in a Master Excel sheet I am creating. It is a Tolerance Table that I need to put several times across the sheet and across the book. I know it can be done in VBA but I am not an expert in VBA. Of course I tried to write the whole formula in EXCEL although I know it was not going to be accepted. Average % intervals________Tolerance 99 and 2_____________________ 2 97-98 and 3-4_________________3 94-96 and 5-7_________________4 91-93 and 8-10________________5 87-90 and 11-14_______________6 82-86 and 15-19_______________7 76-81 and 20-25_______________8 70-75 and 26-31_______________9 60-69 and 32-41______________10 51-59 and 42-50______________11 =IF(CELL=2,"2",IF(CELL=4,"3",IF(CELL=7,"4",IF(CELL =10,"5",IF(CELL=14,"6",IF(CELL=19,"7",IF(CELL=25," 8",IF(CELL=31,"9",IF(CELL=41,"10",IF(CELL=59,"11", IF(CELL=69,"10",IF(CELL=75,"9",IF(CELL=81,"8",IF(C ELL=86,"7",IF(CELL=90,"6",IF(CELL=93,"5",IF(CELL=9 6,"4",IF(CELL=98,"3",IF(CELL=99,"2"))))))))))))))) )))) -- Palencia1978 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Limit to nested Vlookups | Excel Discussion (Misc queries) | |||
Max limit of 7 nested loops | Excel Worksheet Functions | |||
how can I exceed the nested if fuction limit | Excel Discussion (Misc queries) | |||
how can I exceed the nested if fuction limit | Excel Discussion (Misc queries) | |||
Nested IF limit or Open parentheses limit | Excel Discussion (Misc queries) |