Nested IF levels have changed...

[Moo the Dog]

Excel is limited to 7 nested IF statements

I have seen this statement (or something very similar) posted a few times recently on the Forum, and wanted to update anyone who isn't aware...

The 7 Nested IF limit has been raised to 64 nested levels since Excel 2007.
http://office.microsoft.com/en-us/ex...aspx#BMformula

This formula works perfectly fine in Excel 2007 & 2010:
=IF(A1=1,1,IF(A1=2,2,IF(A1=3,3,IF(A1=4,4,IF(A1=5,5,IF(A1=6,6,IF(A1=7,7,IF(A1=8,8,IF(A1=9,9,IF(A1=10,10,"nope"))))))))))

Not that you would WANT to use it.... but it is valid. Of course, if a user is still using 10 year old software (2003), then the max is still 7 levels.

- Moo

[FDibbins]

Good post Moo, thanks for the update

[Andrew-R]

=IF(A1=1,1,IF(A1=2,2,IF(A1=3,3,IF(A1=4,4,IF(A1=5,5,IF(A1=6,6,IF(A1=7,7,IF(A1=8,8,IF(A1=9,9,IF(A1=10,10,"nope"))))))))))

Not that you would WANT to use it

Indeed not, this version avoids nested IFs:

=IF(NOT(ISERROR(MATCH(A1,INDEX(ROW(INDIRECT("1:10")),0),0))),VALUE(MID(CELL("Address",OFFSET(A1,A1-1,0)),FIND("~",SUBSTITUTE(CELL("Address",OFFSET(A1,A1-1,0)),"$","~",2))+1,255)),"Nope")

[Moo the Dog]

LOL... Andrew-R... you win hands down for complexity.

- Moo

[daddylonglegs]

Excel is limited to 7 nested IF statements

Yes, the limit increased to 64 in Excel 2007 - but note that it's always been a limitation on nested functions. You can't nest more than 7 functions, of any type, in Excel 2003, so this formula works in Excel 2007 but not in Excel 2003

=RIGHT(LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE("nested","n","f"),"e","u",1),"s","n"),"t","c"),"e","t"),"d","io"),"io","ion"),8),8)