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)

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

--
Biff
Microsoft Excel MVP


"Ed C" <Ed wrote in message
...
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)

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...

"Ed C" wrote:

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)

On Mon, 6 Oct 2008 14:36:32 -0700, Ed C <Ed wrote:

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

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.
"Ron Rosenfeld" wrote:

On Mon, 6 Oct 2008 14:36:32 -0700, Ed C <Ed wrote:

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

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!

--
Biff
Microsoft Excel MVP


"Ed C" wrote in message
...
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.
"Ron Rosenfeld" wrote:

On Mon, 6 Oct 2008 14:36:32 -0700, Ed C <Ed
wrote:

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

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!


"T. Valko" wrote:

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!

--
Biff
Microsoft Excel MVP


"Ed C" wrote in message
...
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.
"Ron Rosenfeld" wrote:

On Mon, 6 Oct 2008 14:36:32 -0700, Ed C <Ed
wrote:

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

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.

"Ed C" wrote:

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!


"T. Valko" wrote:

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!

--
Biff
Microsoft Excel MVP


"Ed C" wrote in message
...
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.
"Ron Rosenfeld" wrote:

On Mon, 6 Oct 2008 14:36:32 -0700, Ed C <Ed
wrote:

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