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,""))))))))

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,""))))))))

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,""))))))))