Inconsistent formula evaluation in excel

[Guest]

I have come across a somewhat frustrating problem in Excel 2002 when using if
statements and then an evaluation which uses indirect addressing and match
statements to perform an evaluation (subject to some preconditions in the if
statement). My evaluation statement
[MAX(E6:INDIRECT(ADDRESS(ROW(B6)-1+MATCH(B6+L$4/24-($B$7-Starttime),(B6:indirect(ADDRESS(ROW(B6)+L$4/(($B$7-Starttime)*24),COLUMN(B6)))),0),COLUMN(E6))))]
works fine on its own, and works equally well inside a single if but, as soon
as I put two or more nested if statements before the evaluation, Excel sates
that there is an error in the formula and will only accept the formula once I
have replaced the indirect(address()) statements in the match statement with
fixed addresses (which sort of defeats the whole point).
I am using this rather convoluted formula as I have a real world data set
where occasionally data is completely missing and sometimes individual data
points are blank but I need to perform an evaluation over a fixed time span -
the indirect addressing therefore defines the range I need to act upon, which
varies, as I say, over a large data set (perhaps 34000 rows).
What I really cannot understand is why, when my evaluation statement works
fine, and when it works fine as the final part of a simple if, what Excel
might be objecting to when I put it as the final part of a 3 level deep
nested if... I have checked and quadruple checked that the syntax is correct
and that all brackets are matched and correct, etc, but cannot find any error
in the formula and cannot find a way of getting Excel to accept my formula
entry.
For info, her is a simple version that works followed by a simple version
that does not - although they nominally do exactly the same thing!
1 works
[=IF(OR(E6="",E101=""),"",(MAX(E6:INDIRECT(ADDRESS(ROW(B6)-1+MATCH(B6+L$4/24-($B$7-Starttime),B6:INDIRECT(ADDRESS(ROW(B6)+L$4/(($B$7-Starttime)*24),COLUMN(B6))),0),COLUMN(E6))))))]

2 doesn't work
[=IF(E6="","",if(E101="","",MAX(E6:INDIRECT(ADDRESS(ROW(B6)-1+MATCH(B6+L$4/24-($B$7-Starttime),B6:INDIRECT(ADDRESS(ROW(B6)+L$4/(($B$7-Starttime)*24),COLUMN(B6))),0),COLUMN(E6))))))]

Any help or advice would be greatly appreciated.

Many thanks and best wishes

 

[Bob Phillips]

You have too many nested functions (7 is the limit). You need to break the
formula down into component parts, put some part in another cell, and refer
to that.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Harlan Grove]

Boris wrote...

I have come across a somewhat frustrating problem in Excel 2002 when using if
statements and then an evaluation which uses indirect addressing and match
statements to perform an evaluation (subject to some preconditions in the if
statement). My evaluation statement
[reformatted]
MAX(
E6:INDIRECT(
ADDRESS(
ROW(
B6
)-1+MATCH(
B6+L$4/24-($B$7-Starttime),
(B6:INDIRECT(
ADDRESS(
ROW(
B6
)+L$4/(($B$7-Starttime)*24),
COLUMN(
B6
)
)
)),
0
),
COLUMN(
E6
)
)
)
)

works fine on its own, and works equally well inside a single if but, as soon
as I put two or more nested if statements before the evaluation, Excel sates
that there is an error in the formula and will only accept the formula once I
have replaced the indirect(address()) statements in the match statement with
fixed addresses (which sort of defeats the whole point).

....

This is just how Excel works. This has NOTHING whatsoever to do with
formula EVALUATION but everything to do with formula parsing. You're
running into Excel's limit of 7 levels of nested function calls. Your
term above uses six levels of nested calls, so wrapping it inside one
IF call just hits the 7 level of nested calls. Trying to wrap it inside
multiple levels of nested calls exceeds this limit.

There's NOTHING you can do about this in current versions except
rewrite your formula, possibly using multiple cells or defined names to
absorb some of the nested call levels.

Fortunately in this case there are a few idioms that can be replaced
with fewer nested function calls. First, INDIRECT(ADDRESS(...)) can
ALWAYS be replaced by simpler expressions. For instance,

(B6:INDIRECT(ADDRESS(ROW(B6)+L$4/(($B$7-Starttime)*24),COLUMN(B6))))

is a subrange within B6:B65536. You could replace the volatile INDIRECT
construct with a volatile OFFSET construct such as

OFFSET(B6,0,0,1+L$4/($B$7-Starttime)/24,1)

thus eliminating 2 levels of nested function calls. Or you could use
INDEX and eliminate volatility as well.

B6:INDEX(B$7:B$65536,L$4/($B$7-Starttime)/24)

also eliminating 2 levels of nested function calls. Using the INDEX
simplification, try

=MAX(E6:INDEX(E$6:E$65536,MATCH(B6+L$4/24-($B$7-Starttime),
B6:INDEX(B$7:B$65536,L$4/($B$7-Starttime)/24),0)))

which involves only 2 levels of nested function calls.

 

[Guest]

Dear Harlan,
Many thanks for the excellent suggestion and deatailed explanation. There
was me thinking the limit of seven nested if statements meant that I cold
have up to seven "if" statements and I did not appreciate the subtleties it
referring to seven nested function calls. Both your suggestions will work
fine for me (as did the suggestion of splitting the calculation up in to
several cells - which I had already done). The index suggestion is very
straight forward to implement by simple search and replace so this is, I
think, what I shall ne using for now - and I will banish all my
indirect(address()) functions in future.
Many thanks again and very best wishes, Boris.

Boris wrote...

I have come across a somewhat frustrating problem in Excel 2002 when using if
statements and then an evaluation which uses indirect addressing and match
statements to perform an evaluation (subject to some preconditions in the if
statement). My evaluation statement
[reformatted]
MAX(
E6:INDIRECT(
ADDRESS(
ROW(
B6
)-1+MATCH(
B6+L$4/24-($B$7-Starttime),
(B6:INDIRECT(
ADDRESS(
ROW(
B6
)+L$4/(($B$7-Starttime)*24),
COLUMN(
B6
)
)
)),
0
),
COLUMN(
E6
)
)
)
)

works fine on its own, and works equally well inside a single if but, as soon
as I put two or more nested if statements before the evaluation, Excel sates
that there is an error in the formula and will only accept the formula once I
have replaced the indirect(address()) statements in the match statement with
fixed addresses (which sort of defeats the whole point).

....

This is just how Excel works. This has NOTHING whatsoever to do with
formula EVALUATION but everything to do with formula parsing. You're
running into Excel's limit of 7 levels of nested function calls. Your
term above uses six levels of nested calls, so wrapping it inside one
IF call just hits the 7 level of nested calls. Trying to wrap it inside
multiple levels of nested calls exceeds this limit.

There's NOTHING you can do about this in current versions except
rewrite your formula, possibly using multiple cells or defined names to
absorb some of the nested call levels.

Fortunately in this case there are a few idioms that can be replaced
with fewer nested function calls. First, INDIRECT(ADDRESS(...)) can
ALWAYS be replaced by simpler expressions. For instance,

(B6:INDIRECT(ADDRESS(ROW(B6)+L$4/(($B$7-Starttime)*24),COLUMN(B6))))

is a subrange within B6:B65536. You could replace the volatile INDIRECT
construct with a volatile OFFSET construct such as

OFFSET(B6,0,0,1+L$4/($B$7-Starttime)/24,1)

thus eliminating 2 levels of nested function calls. Or you could use
INDEX and eliminate volatility as well.

B6:INDEX(B$7:B$65536,L$4/($B$7-Starttime)/24)

also eliminating 2 levels of nested function calls. Using the INDEX
simplification, try

=MAX(E6:INDEX(E$6:E$65536,MATCH(B6+L$4/24-($B$7-Starttime),
B6:INDEX(B$7:B$65536,L$4/($B$7-Starttime)/24),0)))

which involves only 2 levels of nested function calls.

 

[Guest]

Dear Bob,
Many thanks. I had thought that you can have up to seven nested "if"
statements and had not appreciated that this limit was actually for seven
nested function calls...
Harlan Grove gave a detailed reply with analysis of the nesting and two
simple alternative constructions to do the same calculation with two less
nested calls (using offset or index) and I shall certainly be changing the
construction to use the index method.
Many thanks to both of you for your replies.
Best wishes, Boris.

You have too many nested functions (7 is the limit). You need to break the
formula down into component parts, put some part in another cell, and refer
to that.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

I have come across a somewhat frustrating problem in Excel 2002 when using if
statements and then an evaluation which uses indirect addressing and match
statements to perform an evaluation (subject to some preconditions in the if
statement). My evaluation statement
[MAX(E6:INDIRECT(ADDRESS(ROW(B6)-1+MATCH(B6+L$4/24-($B$7-Starttime),(B6:indi
rect(ADDRESS(ROW(B6)+L$4/(($B$7-Starttime)*24),COLUMN(B6)))),0),COLUMN(E6)))
)]
works fine on its own, and works equally well inside a single if but, as soon
as I put two or more nested if statements before the evaluation, Excel sates
that there is an error in the formula and will only accept the formula once I
have replaced the indirect(address()) statements in the match statement with
fixed addresses (which sort of defeats the whole point).
I am using this rather convoluted formula as I have a real world data set
where occasionally data is completely missing and sometimes individual data
points are blank but I need to perform an evaluation over a fixed time span -
the indirect addressing therefore defines the range I need to act upon, which
varies, as I say, over a large data set (perhaps 34000 rows).
What I really cannot understand is why, when my evaluation statement works
fine, and when it works fine as the final part of a simple if, what Excel
might be objecting to when I put it as the final part of a 3 level deep
nested if... I have checked and quadruple checked that the syntax is correct
and that all brackets are matched and correct, etc, but cannot find any error
in the formula and cannot find a way of getting Excel to accept my formula
entry.
For info, her is a simple version that works followed by a simple version
that does not - although they nominally do exactly the same thing!
1 works
[=IF(OR(E6="",E101=""),"",(MAX(E6:INDIRECT(ADDRESS(ROW(B6)-1+MATCH(B6+L$4/24
-($B$7-Starttime),B6:INDIRECT(ADDRESS(ROW(B6)+L$4/(($B$7-Starttime)*24),COLU
MN(B6))),0),COLUMN(E6))))))]

2 doesn't work
[=IF(E6="","",if(E101="","",MAX(E6:INDIRECT(ADDRESS(ROW(B6)-1+MATCH(B6+L$4/2
4-($B$7-Starttime),B6:INDIRECT(ADDRESS(ROW(B6)+L$4/(($B$7-Starttime)*24),COL
UMN(B6))),0),COLUMN(E6))))))]

Any help or advice would be greatly appreciated.

Many thanks and best wishes