Excel Formula Error for No Good Reason

[Wayne]

I don't know why, but this formula will not stop giving me an error in
Excel 2003.
=IF( $A8 <> "",
B8 * E8,
IF( ROW(H7) <> 1,
IF( G8 = "Subtotal",
SUM( H$2:H7 ),
IF( LEFT(G8, 3) = "Tax",
ROUND( $J$1 * H7, 2),
IF( G8 = "Total",
INDIRECT( ADDRESS( ROW(H8)-2, COLUMN(H8) ) ) + H7,
IF( G8 = "Depr",
SUM( INDIRECT( "G2:"&ADDRESS( ROW(H8) - 4,
COLUMN(G7),4) ,TRUE) ),
""
)
)
)
),
""
)
)

The error is being targeted at the second ADDRESS function, but that
works in a separate cell and in the formula debugger is outputting the
correct reference. Indirect is receiving the right string, in this
case: "G2:G4" It just will not let me past this error.

Is this a limitation in Excel? Can anyone see a problem with my
function?

Thanks,
-Wayne

 

[Ron Rosenfeld]

I don't know why, but this formula will not stop giving me an error in
Excel 2003.
=IF( $A8 <> "",
B8 * E8,
IF( ROW(H7) <> 1,
IF( G8 = "Subtotal",
SUM( H$2:H7 ),
IF( LEFT(G8, 3) = "Tax",
ROUND( $J$1 * H7, 2),
IF( G8 = "Total",
INDIRECT( ADDRESS( ROW(H8)-2, COLUMN(H8) ) ) + H7,
IF( G8 = "Depr",
SUM( INDIRECT( "G2:"&ADDRESS( ROW(H8) - 4,
COLUMN(G7),4) ,TRUE) ),
""
)
)
)
),
""
)
)

The error is being targeted at the second ADDRESS function, but that
works in a separate cell and in the formula debugger is outputting the
correct reference. Indirect is receiving the right string, in this
case: "G2:G4" It just will not let me past this error.

Is this a limitation in Excel? Can anyone see a problem with my
function?

Thanks,
-Wayne

Excel 2003 (and earlier) only allows seven levels of nesting. In your formula,
that second ADDRESS function invokes an eight level of nesting.

One solution, short of upgrading to Excel 2007, would be to split your formula
into two cells.

By the way, it is my understanding that the limitation is due to Excel's
formula parser, and that, by using Open Office, you can develop a formula with
increased levels of nesting there, then convert to Excel 2003. But I have no
experience in that. Nor do I know if you could develop the formula in Excel
2007, and then use it in 2003.
--ron

 

[PCLIVE]

Chance are that you're reaching the maximum number of nested functions.

 

[Harlan Grove]

....
[reformatted to show ALL nesting levels]

=IF( $A8 <> "",
   B8 * E8,
   IF( ROW(H7) <> 1,
    IF( G8 = "Subtotal",
       SUM( H$2:H7 ),
       IF(
LEFT(G8, 3) = "Tax",
        ROUND( $J$1 * H7, 2),
        IF( G8 = "Total",
           INDIRECT(
ADDRESS(
ROW(H8)-2,
COLUMN(H8)
)
) + H7,
         IF( G8 = "Depr",
            SUM(
INDIRECT( "G2:"&
____1_2_3_4_5_6_7_8

ADDRESS(
ROW(H8) - 4,
COLUMN(G7),
4
),
TRUE
)
),
            ""
          )
        )
      )
    ),
    ""
  )
)

....

Others have pointed out that you're hitting the 7 nested function call
limit. The good news is that you could rewrite your formula with fewer
nested function calls.

I'll assume you need everything in your formula, but the
IF(ROW(H7)<>1, test seems pointless unless you'd be copying or
filling this formula into cells above the one originally containing
this formula so that this would become IF(ROW(H1)<>1, which is the
only way this condition could be FALSE.

Also the 2nd arg to the 1st IF is an unrounded product of 2 cells, but
the 2nd arg to the 4th IF is a rounded product of 2 different cells.
Do you really want some products rounded and others unrounded?

___1_2_3_4_5

=IF( $A8 <> "",
B8 * E8,
IF( ROW(H7) = 1,
"",
CHOOSE(
1+SUMPRODUCT(--
(IF(LEFT(G8,3)="Tax","Tax",G8)={"Subtotal";"Tax";"Total";"Depr"}),
{1;2;3;4}),
"",
SUM( H$2:H7 ),
ROUND( $J$1 * H7, 2),
INDEX($1:$65536, ROW(H8)-2, COLUMN(H8)) + H7,
SUM(
$G$2:INDEX($1:$65536,
ROW(H8) - 4,
COLUMN(G7)
)
)
)
)
)


This uses only 5 levels of nested function calls. It could be reduced
further depending on why you're using original constructs like
INDIRECT(ADDRESS(ROW(H8)-2,COLUMN(H8))). If you're using
INDIRECT(ADDRESS(...)) in the belief that it'll protect against row/
column insertion/deletion, it WON'T in this particular case because
row/column insertion/deletion will affect the references to G7, H7 and
H8 in the ROW and COLUMN calls.

I'd guess you're entering this formula in a cell in row 8. If so, an
equivalent formula would be

=IF( $A8 <> "",
B8 * E8,
IF( ROW(H7) = 1,
"",
CHOOSE(
1+SUMPRODUCT(--
(IF(LEFT(G8,3)="Tax","Tax",G8)={"Subtotal";"Tax";"Total";"Depr"}),
{1;2;3;4}),
"",
SUM( H$2:H7 ),
ROUND( $J$1 * H7, 2),
H6 + H7,
SUM( G$2:G4 )
)
)
)
)

This uses only 3 levels of nested function calls.

Why do you believe you need to use INDIRECT(ADDRESS(...)) constructs?
Note that even if you have some reason for doing so, the expression

INDIRECT(ADDRESS(row_expression,column_expression))

can ALWAYS be replaced by

INDIRECT("R" & row_expression, "C" & column_expression, 0)

which uses one fewer nested function call level. The
INDIRECT(ADDRESS(...)) construct is NEVER necessary. There are ALWAYS
alternatives that use FEWER nested function call levels. Putting this
another way, the ADDRESS function serves no useful purpose other than
to include range addresses in formula RESULTS.

 

[Wayne]

Ron, Harlan, and all others,

I appreciate your responses, knowing that Excel 2003 is limited to 7
nests is extremely helpful in knowing how to fix the problem. I had
searched for "Excel nesting bug" but didn't turn up anything useful,
so I figured that wasn't the case. As Ron had suggested, I was able to
work around it by simply putting the values I need in some hidden
cells off of the main part of the sheet. It feels like a hack, but it
works for now.

IF(ROW(H7)<>1,  test seems pointless unless you'd be copying or
filling this formula into cells above the one originally containing
this formula so that this would become  IF(ROW(H1)<>1,  which is the
only way this condition could be FALSE.

Correct, this snippet is down several rows from the top of where the
formula was filled down, only because that was the cell I was
debugging the formula in. In the interest of maintenance, ease of fill
down, etc, and because I wasn't aware of any nesting limitations, I
chose to put such a formula in so that the cell wouldn't return an
error when expecting a number from the header row above it.

Also the 2nd arg to the 1st IF is an unrounded product of 2 cells, but
the 2nd arg to the 4th IF is a rounded product of 2 different cells.
Do you really want some products rounded and others unrounded?

I hadn't considered it, I guess because all of my testing didn't
reveal any rounding errors on that first one. Good point, though.

If you're using
INDIRECT(ADDRESS(...)) in the belief that it'll protect against row/
column insertion/deletion, it WON'T in this particular case because
row/column insertion/deletion will affect the references to G7, H7 and
H8 in the ROW and COLUMN calls.

The obfuscated cell references are there for maintenance, again. And,
even though I won't ever reference it, copy/paste and fill down will
try to reference a cell that doesn't exist if I'm higher up on the
spreadsheet. This forumla is actually intended to be automatically
shifted by Excel, because I want it to change on fill down starting at
row 2 going for 200 cells below.

I'd guess you're entering this formula in a cell in row 8. If so, an
equivalent formula would be

[formulas removed]

This uses only 3 levels of nested function calls.

I'll see if I can make use of some of that. It looks simpler, but I
need to step through it with my data.

Why do you believe you need to use INDIRECT(ADDRESS(...)) constructs?

I don't remember how long ago, but that was how I had seen indirects
done. I was aware of the R1C1 option for INDIRECT, but maybe it made
it easier for debugging? Regardless, good idea to dump another nest
level.

Thanks for all your help. I'll give these a go.

-Wayne