Nested Function too Large?

[proballin]

I have a nested function:

=IF(AND(NOT(ISERR(FIND("CH",K2))),MONTH(B2)=3),S2+Spread!E$12,IF(AND(NOT(ISERR(FIND("CK",K2))),MONTH(B2)=5),S2+Spread!E$13,IF(AND(NOT(ISERR(FIND("CN",K2))),MONTH(B2)=7),S2+Spread!E$14,IF(AND(NOT(ISERR(FIND("CU",K2))),MONTH(B2)=9),S2+Spread!E$15, IF(AND(NOT(ISERR(FIND("CZ",K2))),MONTH(B2)=12),S2+Spread!E$16, S2)))))

I get an error whenever I try and save it saying "The Formula you typed contains an error".

However if I remove that last IF statement it works. Anyone know of a either an alternative to this statement or how to get the whole function to fit and work?

 

[Brianwarnock]

This is some kind of function overload, not sure how this works as the help only says no more than 7 therefore having 4 If(And(Not(iserror( should fail.
Removal of the 5th AND or Not(Iserror allows the formaula to be accepted

So I think that you will need to bite the bullet and write a function to do this, its annoying since when using fx to test the formulas parts it all works.

Brian

 

[proballin]

Luckily I found a way around it. This is what I ended up using:

=IF(AND(Spread!$F$12 = LEFT($K2,2),MONTH($B2)=3),S2+Spread!$E$12, IF(AND(Spread!$F$13 = LEFT($K2,2),MONTH($B2)=5),S2+Spread!$E$13, IF(AND(Spread!$F$14 = LEFT($K2,2),MONTH($B2)=7),S2+Spread!$E$14, IF(AND(Spread!$F$15 = LEFT($K2,2),MONTH($B2)=9),S2+Spread!$E$15, IF(AND(Spread!$F$16 = LEFT($K2,2),MONTH(B2)=12),S2+Spread!$E$16,S2)))))

I got rid of the NOT, ISERR and FIND Functions and just use the LEFT function for my comparison. Close call but got around it!

 

[Brianwarnock]

Well I'm glad you got round it . but that does not look much like the original and it looks messy enough for me to use a function which can be documented and more easily changed

Brian