I'm aware of the seven level limit. The formula had six levels, and it won't
accept the seventh. That is what is frustrating me.

"vandenberg p" wrote:

Hello:

You hit the limit of nested if's. From the Excel help file:

Nesting level limits A formula can contain up to seven levels of nested functions. When
Function B is used as an argument in Function A, Function B is a second-level function. For
instance, the AVERAGE function and the SUM function are both second-level functions because
they are arguments of the IF function. A function nested within the AVERAGE function would be
a third-level function, and so on.


Pieter Vandenberg


manxman wrote:
: At the start of this formula, I want to add a 7th IF statement that checks
: the condition of another cell, and sets this whole formula to "--" if the
: other cell is "". When I try to add it, I get a standard formula error
: message box, and then the last MAX is highlighted. What's going on?
: =IF((AND((X2+Y2)=MAX((X2+Y2),(AF2+AG2),(AN2+AO2),( AT2+AU2),(AZ2+BA2),(BF2+BG2)),(X2+Y2)=BQ2),V6,IF
: {and so on five more times, checking next AF+AG. The formula ends with: if
: false, "--". (I have left out the $ before each cell reference for brevity.
: The entire formula is about 700 to 900 characters long, depending how far
: down the sheet it is, but I can't add the 7th IF statement to even the
: shortest one.) What prompted the need for the 7th if statement is
: that without the underlying data cells being filled in, a #VALUE! error is
: reported. Cells X,Y,AF,AG, etc. are themselves formulas, and are set to
: equal "" when the underlying data are absent. I have two other formulas very
: similar to this one, one checking just X,AF,AN, etc, and the other checking
: Y, AG,AO, etc. They don't report the error message. Only the formula with
: the combined X+Y, AF+AG, etc is a problem. There is also another combined
: X+Y, AF+AG, etc formula showing the same error message, but it only has 3 IF
: statments, and I am able to add the beginning 4th to cure the problem.

: Thanks in advance for any help
:
: