| Home |
| Search |
| Today's Posts |
|
|
|
#1
|
|||
|
|||
Max IF Statement Q
Could anyone tell me if I have exceeded the allowed number of IF statements
in the formula below? It seems to stop working correctly at the second formula below - is there a work around? =IF(Masters!K1="London",(IF(AND(AA10<18),'JLC Rates'!D$14,(IF(AND(AA12<1,AA10=18),'JLC Rates'!D$10,(IF(AND(AA121,AA10<19,AA10=18),'JLC Rates'!D$10,IF(AND(AA121,AA10=19),'JLC Rates'!D$12,""))))))),(IF(AND(AA10<18),'JLC Rates'!G$14,(IF(AND(AA12<1,L10=18),'JLC Rates'!G$10,(IF(AND(AA121,AA10<19,AA10=18),'JLC Rates'!G$10,IF(AND(AA121,AA10=19),'JLC Rates'!G$12,"")))))))) Part of above (IF(AND(AA10<18),'JLC Rates'!G$14,(IF(AND(AA12<1,L10=18),'JLC Rates'!G$10,(IF(AND(AA121,AA10<19,AA10=18),'JLC Rates'!G$10,IF(AND(AA121,AA10=19),'JLC Rates'!G$12,"")))))))) |
|
#2
|
|||
|
|||
|
You can have 7 nested IFs, you have 8, you have exceeded the max. You might
get away with making the last IF in each K1 case a default, but here is another suggestion =INDIRECT(IF(Masters!K1="London","'JLC Rates'!D$","'JLC Rates'!G$")&(IF(AA10<18,14,(IF(AND(AA12<1,AA10=18 ),10,(IF(AND(AA121,AA10<1 9,AA10=18),10,IF(AND(AA121,AA10=19),12,"")))))) )) -- HTH RP (remove nothere from the email address if mailing direct) "John" wrote in message ... Could anyone tell me if I have exceeded the allowed number of IF statements in the formula below? It seems to stop working correctly at the second formula below - is there a work around? =IF(Masters!K1="London",(IF(AND(AA10<18),'JLC Rates'!D$14,(IF(AND(AA12<1,AA10=18),'JLC Rates'!D$10,(IF(AND(AA121,AA10<19,AA10=18),'JLC Rates'!D$10,IF(AND(AA121,AA10=19),'JLC Rates'!D$12,""))))))),(IF(AND(AA10<18),'JLC Rates'!G$14,(IF(AND(AA12<1,L10=18),'JLC Rates'!G$10,(IF(AND(AA121,AA10<19,AA10=18),'JLC Rates'!G$10,IF(AND(AA121,AA10=19),'JLC Rates'!G$12,"")))))))) Part of above (IF(AND(AA10<18),'JLC Rates'!G$14,(IF(AND(AA12<1,L10=18),'JLC Rates'!G$10,(IF(AND(AA121,AA10<19,AA10=18),'JLC Rates'!G$10,IF(AND(AA121,AA10=19),'JLC Rates'!G$12,"")))))))) |
|
#3
|
|||
|
|||
|
Thanks Bob
"Bob Phillips" wrote in message ... You can have 7 nested IFs, you have 8, you have exceeded the max. You might get away with making the last IF in each K1 case a default, but here is another suggestion =INDIRECT(IF(Masters!K1="London","'JLC Rates'!D$","'JLC Rates'!G$")&(IF(AA10<18,14,(IF(AND(AA12<1,AA10=18 ),10,(IF(AND(AA121,AA10<1 9,AA10=18),10,IF(AND(AA121,AA10=19),12,"")))))) )) -- HTH RP (remove nothere from the email address if mailing direct) "John" wrote in message ... Could anyone tell me if I have exceeded the allowed number of IF statements in the formula below? It seems to stop working correctly at the second formula below - is there a work around? =IF(Masters!K1="London",(IF(AND(AA10<18),'JLC Rates'!D$14,(IF(AND(AA12<1,AA10=18),'JLC Rates'!D$10,(IF(AND(AA121,AA10<19,AA10=18),'JLC Rates'!D$10,IF(AND(AA121,AA10=19),'JLC Rates'!D$12,""))))))),(IF(AND(AA10<18),'JLC Rates'!G$14,(IF(AND(AA12<1,L10=18),'JLC Rates'!G$10,(IF(AND(AA121,AA10<19,AA10=18),'JLC Rates'!G$10,IF(AND(AA121,AA10=19),'JLC Rates'!G$12,"")))))))) Part of above (IF(AND(AA10<18),'JLC Rates'!G$14,(IF(AND(AA12<1,L10=18),'JLC Rates'!G$10,(IF(AND(AA121,AA10<19,AA10=18),'JLC Rates'!G$10,IF(AND(AA121,AA10=19),'JLC Rates'!G$12,"")))))))) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| IF Statement with Average Function results in #Value! | Excel Discussion (Misc queries) | |||
| 7+ nested if statement? | Excel Worksheet Functions | |||
| Statement | Excel Worksheet Functions | |||
| Duplicate fields does not match up! If statement | Excel Worksheet Functions | |||
| If statement needed | Excel Worksheet Functions | |||
Hybrid Mode
