Max IF Statement Q

[John]

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(AA12>1,AA10<19,AA10>=18),'JLC
Rates'!D$10,IF(AND(AA12>1,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(AA12>1,AA10<19,AA10>=18),'JLC
Rates'!G$10,IF(AND(AA12>1,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(AA12>1,AA10<19,AA10>=18),'JLC
Rates'!G$10,IF(AND(AA12>1,AA10>=19),'JLC Rates'!G$12,""))))))))

 

[Bob Phillips]

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(AA12>1,AA10<1
9,AA10>=18),10,IF(AND(AA12>1,AA10>=19),12,""))))))))

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[John]

Thanks Bob

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