Nested IF compatability error with Excel 2007

[Ed C]

I am using Excel 2007, co-workers still on Excel 2003. When I work on
something, I save to 97-2003 .xls to be able to share files. I only use
Excel 2007 file save when I am downloading data from a database that needs to
use more rows..anyway...I had a file someone shared with me (created in Excel
2003) and it had a nested if statement with 11 levels. I used the file but
when I went to save it as 97-2003, I received a compatability warning about
only allowing 7 levels..is that due to versions 97, 2000, 2002 of Excel??
(Oddly enought, if I save to 95/5.0, it doesn't give me that compatability
message)

 

[T. Valko]

I received a compatability warning about only allowing 7

levels..is that due to versions 97, 2000, 2002 of Excel??

Yes. You almost never need to use 11 nested IF functions.

You can probably get the same result using a lookup table.

See if this helps:

http://contextures.com/xlFunctions02.html

 

[Sean Timmons]

2003 does only allow up to 7 levels of IF.

May need to use a different formula to resolve your issue. VB with a
CASE...SELECT statement at worst...

 

[Ron Rosenfeld]

I am using Excel 2007, co-workers still on Excel 2003. When I work on
something, I save to 97-2003 .xls to be able to share files. I only use
Excel 2007 file save when I am downloading data from a database that needs to
use more rows..anyway...I had a file someone shared with me (created in Excel
2003) and it had a nested if statement with 11 levels. I used the file but
when I went to save it as 97-2003, I received a compatability warning about
only allowing 7 levels..is that due to versions 97, 2000, 2002 of Excel??
(Oddly enought, if I save to 95/5.0, it doesn't give me that compatability
message)

It would be interesting to try it and see what happens. I believe Harlan has
written that the problem with the nesting limitation in pre-Excel 2007 lies
with the formula parser. But that formulas with more nesting than the seven
can be developed in Open Office, and then saved and re-opened in earlier
versions of Excel.

If that also holds true for Excel 2007, the end result may be that your
colleagues will be able to use the sheet, but, of course, they may not be able
to edit that formula.
--ron

 

[Ed C]

Thanks everyone...of course, I have already modified the formula to a vlookup
which works just fine...just was trying to understand which version of
Microsoft Excel was the culprit in terms of limiting the nested if statement
levels to 7 or less...the file that had the issue was one that I inherited,
created by someone in Excel 2003...so I am guessing Excel 2003 was able to
handle more than 7 nested if levels.

The person who originally gave me the file to work on with the nested if
statements (to whom I have returned the updated file) was wondering why I
needed to update their file to get rid of the nested if 7+ level
formula...didn't really have a good answer other than when I tried to save to
an Excel 97-2003 from Excel 2007, I got compatability warnings.

Still not really getting the answer but do appreciate the suggestions.

 

[T. Valko]

In Excel 2007 you can nest up to 64 levels. In all other versions you can
nest up to 7 levels.

You said this did work in Excel 95, I can't remember that far back!

 

[Ed C]

Suprisingly....you say the nested if didn't work in prior version beyond 7
levels...but the file I received was created in Excel 2003 and had 11
levels...seemed to work fine with the originator's Excel 2003...guess it is a
mystery...when the Excel 2003 person opened the file after I saved it to a
97-2003 file, their nested ifs cells no longer had the 11 level nested if
formula but had numeric values yet the cell really contained a #VALUE that
they didn't see until they updated the particular cell to look for their
nested if...very spooky!

 

[Sean Timmons]

Odd since Excel 2003 help file states can only handle up to 7 nested if's.
Can only guess as to how that could possibly been bypassed.