Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Exceeding the limit of Nest IF's
I have a worksheet where I have used Data Validation taken from
another worksheet for entry in 2 Cells(CD19 & CQ19) based on the values in these 2 cells I would like the result to be dispayed in a third Cell(DJ15) I tried the example on Chip Pearson's web page, but that does not work either, since I still will exceed 7 IF's. Is there another way to do this. Named Range P1 =IF(AND(CD19=1,CD19<=8,CQ19=1,CQ19<=8),"P-N°1 - P-N°1","Not Qualified") Named Range P5 =IF(OR(CQ19="10A",CQ19="10B",CQ19="10C",CQ19="10C" ,CQ19="10H",CQ19="10I",CQ19="10J"_ ,CQ19="10K")*OR(CD19="10A",CD19="10B",CD19="10C",C D19="10C",CD19="10H",CD19="10I",_ CD19="10J",CD19="10K"),"P-N° 10 to 10",IF(OR(CD19="11A",_ CD19="11B")*OR(CQ19="11A",CQ19="11B"),"P-N° 11 to 11",IF(OR(CD19="5A", CD19="5B",_CD19="5C")*OR(CQ19="5A",CQ19="5B",CQ19= "5C"),"P-N° 5 _ to 5","Not Qualified""))) Named Range P10A =IF(OR(CD19="10A",CD19="10B",CD19="10C",CD19="10C" ,CD19="10H",CD19="10I",_ CD19="10J",CD19="10K")*AND(CQ19=1,CQ19<=8),"P-N° 10A to 1",_ IF(OR(CD19="11A",CD19="11B")*AND(CQ19=1,CQ19<=8), "P-N° 11A to 1",_ IF(OR(CD19="5A",CD19="5B",CD19="5C")*AND(CQ19=1,C Q19<=8),"P-N° 5A to 1",_ IF(OR(CQ19="10A",CQ19="10B",CQ19="10C",CQ19="10C", CQ19="10H",CQ19="10I",_ CQ19="10J",CQ19="10K")*AND(CD19=1,CD19<=8),"P-N° 1 to 10A",_ IF(OR(CQ19="11A",CQ19="11B")*AND(CD19=1,CD19<=8), "P-N° 1 to 11A",_ IF(OR(CQ19="5A",CQ19="5B",CQ19="5C")*AND(CD19=1,C D19<=8),"_ P-N°1 to 5A","Not Qualified"")))))) Named Range P34 =IF(AND(CD19=1,CD19<=8,CQ19=1,CQ19=34),"P-N°1 to P-N° 34",_ IF(AND(CD19=34,CQ19=1,CQ19<=8),"P-N° 34 to P-N° 1",IF(AND(CD19=34,_ CQ19=34),"P-N° 34 to P-N° 34","Not Qualified"))) Named Range P41 =IF(AND(CD19=1,CD19<=8,CQ19=41,CQ19<=47),"P-N° 1 ot P-N° 41-47",_ IF(AND(CQ19=41,CQ19<=47,CD19=41,CD19<=47),"P-N° 41 to P-N° 47",_ IF(AND(CQ19=1,CD19=41,CD19<=47),"P-N° 41-47 to P-N°1","Not_ Qualified""))) Named Range P51 Named Range P61 etc... Any help would be appricated. Kind Regards Martin |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Exceeding the limit of Nest IF's
"Martin Koenig" wrote...
... Named Range P5 =IF(OR(CQ19="10A",CQ19="10B",CQ19="10C",CQ19="10C ",CQ19="10H",CQ19="10I", CQ19="10J",CQ19="10K")*OR(CD19="10A",CD19="10B",C D19="10C",CD19="10C", CD19="10H",CD19="10I",CD19="10J",CD19="10K"),"P-N° 10 to 10", IF(OR(CD19="11A",CD19="11B")*OR(CQ19="11A",CQ19=" 11B"),"P-N° 11 to 11", IF(OR(CD19="5A",CD19="5B",CD19="5C")*OR(CQ19="5A" ,CQ19="5B",CQ19="5C"), "P-N° 5 to 5","Not Qualified""))) Note that you're comparing CQ19 and CD19 to "10C" twice. Any time you're comparing the same cell to multiple values it should be a clear sign you should be using some form of lookup. Enter the following table into a 2-column range. 10A 1 10B 1 10C 1 10H 1 10I 1 10J 1 10K 1 11A 2 11B 2 5A 3 5B 3 5C 3 Define a name like PNTable referring to this range. Then change your formula to =IF(ISNUMBER(1/(LOOKUP(CD19,PNTable)=LOOKUP(CQ19,PNTable)), CHOOSE(LOOKUP(CD19,PNTable),"P-N° 10 to 10","P-N° 11 to 11","P-N° 5 to 5"), "Not Qualified"") and similarly for all your other formulas. -- To top-post is human, to bottom-post and snip is sublime. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Exceeding the limit of Nest IF's
Harlan wrote in message ...
"Martin Koenig" wrote... .. Named Range P5 =IF(OR(CQ19="10A",CQ19="10B",CQ19="10C",CQ19="10C ",CQ19="10H",CQ19="10I", CQ19="10J",CQ19="10K")*OR(CD19="10A",CD19="10B",C D19="10C",CD19="10C", CD19="10H",CD19="10I",CD19="10J",CD19="10K"),"P-N° 10 to 10", IF(OR(CD19="11A",CD19="11B")*OR(CQ19="11A",CQ19=" 11B"),"P-N° 11 to 11", IF(OR(CD19="5A",CD19="5B",CD19="5C")*OR(CQ19="5A" ,CQ19="5B",CQ19="5C"), "P-N° 5 to 5","Not Qualified""))) Note that you're comparing CQ19 and CD19 to "10C" twice. Any time you're comparing the same cell to multiple values it should be a clear sign you should be using some form of lookup. Enter the following table into a 2-column range. 10A 1 10B 1 10C 1 10H 1 10I 1 10J 1 10K 1 11A 2 11B 2 5A 3 5B 3 5C 3 Define a name like PNTable referring to this range. Then change your formula to =IF(ISNUMBER(1/(LOOKUP(CD19,PNTable)=LOOKUP(CQ19,PNTable)), CHOOSE(LOOKUP(CD19,PNTable),"P-N° 10 to 10","P-N° 11 to 11","P-N° 5 to 5"), "Not Qualified"") and similarly for all your other formulas. Just wanted to say thanks, your solution works perfectly for what I was trying to do. How easy something can be if you know what you are doing, again thanks for your assistance. Martin |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculation for amt. Exceeding $106,800 for SS Income Limit | Excel Worksheet Functions | |||
How do I nest more then 7 IF's? | Excel Discussion (Misc queries) | |||
exceeding columns limit | Excel Discussion (Misc queries) | |||
Exceeding the limit for Nested IFs | Excel Worksheet Functions | |||
7+ Nest If's | Excel Worksheet Functions |