Nested Ifs

[Jason O]

Hi,

According to my XL help you can have up to seven nested Ifs in one
statement. However, the following will not work:

=IF(AND(A14>=DATE(2004,3,1),A14<=DATE(2004,8,31)),"04",IF(AND(A14>=DATE(2004
,9,1),A14<=DATE(2004,12,31)),"04
54",IF(AND(A14>=DATE(2005,1,1),A14<=DATE(2005,2,28)),"05
54",IF(AND(A14>=DATE(2005,3,1),A14<=DATE(2005,8,31)),"05",IF(AND(A14>=DATE(2
005,9,1),A14<=DATE(2005,12,31)),"05
55",IF(AND(A14>=DATE(2006,1,1),A14<=DATE(2006,2,28)),"06
55",IF(AND(A14>=DATE(2006,3,1),A14<=DATE(2006,8,31)),"06")))))))


Ignoring for a moment the inelegance (I know a true vlookup would be better
but I do have my reasons there are only seven so why doesn't it work. If
I remove the last IF or swap it for a simple text FALSE return, then no
problems. Can anyone shed any light on this.

Many thanks

Jason

 

[JE McGimpsey]

According to my XL help you can have up to seven nested Ifs in one
statement. However, the following will not work:

=IF(AND(A14>=DATE(2004,3,1),A14<=DATE(2004,8,31)),"04",IF(AND(A14>=DATE(2004
,9,1),A14<=DATE(2004,12,31)),"04
54",IF(AND(A14>=DATE(2005,1,1),A14<=DATE(2005,2,28)),"05
54",IF(AND(A14>=DATE(2005,3,1),A14<=DATE(2005,8,31)),"05",IF(AND(A14>=DATE(2
005,9,1),A14<=DATE(2005,12,31)),"05
55",IF(AND(A14>=DATE(2006,1,1),A14<=DATE(2006,2,28)),"06
55",IF(AND(A14>=DATE(2006,3,1),A14<=DATE(2006,8,31)),"06")))))))

Ignoring for a moment the inelegance (I know a true vlookup would be better
but I do have my reasons there are only seven so why doesn't it work. If
I remove the last IF or swap it for a simple text FALSE return, then no
problems. Can anyone shed any light on this.

You can have up to 7 nested *functions*. Since you call AND() within
your last IF, that exceeds the max.

Note that if you test for dates earlier than 1 March 2004, you can
dispense with the ANDs:

=IF(A14<DATE(2004,3,1),"Too Early", IF(A14<=DATE(2004,8,31), "04",
IF(A14<=DATE(2004,12,31),"04 54",IF(...

since the only way the third IF can be executed is if A14 is NOT less
than or equal to 31 August 2004, which inherently means that it's
greater than or equal to 1 September 2004...

Of course, that adds another layer of IFs.

A lookup is the way to go...