INDIRECT - Strange behavior

[vezerid]

Hi everybody,

once more INDIRECT is producing a behavior I cannot decipher. Problem:

Cells A1:A18 contain various numbers, some of which are 0. A1 is always
a non-zero number. Consecutive zeros are not expected.

In B2:B17 I want a formula, which will produce the sum, next to each 0,
of the numbers since the last 0. This is an older question in the
group, for which Bob Phillips had supplied a solution, involving two
columns. I was trying to see if I can do it in one column. Here is what
I have in B2: (array formula)

{=IF(A2=0,SUM(N(INDIRECT("A"&MAX(1,ROW($A$1:A1)*($A$1:A1=0))&":A"&ROW()))),"")}

This formula produces 1 for the first sum and 0 for all other sums. I
would understand it better if it produced #VALUE! instead.

When I extract the argument to INDIRECT and enter it as a separate
formula in C2,

{="A"&MAX(1,ROW($A$1:A1)*($A$1:A1=0))&":A"&ROW()}

Then the formula in D2 works properly:

=IF(A2=0,SUM(INDIRECT(C2)),"")

Why is this happening? INDIRECT often behaves strangely and I have not
yet found a consistent framework for when it does and when not, despite
various discussions in the NG. Can you enlighten please?

TIA

Kostis Vezerides

 

[Arvi Laanemets]

Hi

For B2
=IF(A1=0,A2,IF(ROW()=2,A1,SUM(B1,A2)))
, and copy down (it is non-array formula)


Arvi Laanemets

 

[vezerid]

Arvi,

thanks for the reply. Unfortunately, not only does it not answer my
question regarding the behavior of INDIRECT, but neither does it solve
the original problem.

Regards,

Kostis

 

[Bob Phillips]

Hi Kostis,

What's the weather like over there?

There is nothing wrong with the INDIRECT, the problem is caused by the fact
that you are trying to pass an array to it rather than a string. This is
caused by the &ROW(), which returns an array. I think you realise this,
which is probably why you added the N function, but that doesn't do it. What
you need to do is force the ROW() out of the array, which you can do with
SUM(ROW()). So the formula is then

=IF(A2=0,SUM(INDIRECT("A"&MAX(1,ROW($A$1:A1)*($A$1:A1=0))&":A"&SUM(ROW()))),
"")

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[vezerid]

Hi Bob,

What's the weather like over there?

Funny you should ask. I am in an island, took time away in order to
write on - guess what - Excel for my future courses. I have limited
access to the internet, which makes every visit to the NG minimalistic
and rather frantic. The weather is disappointing for the more southern
lattitude but this has been a long winter anyway.

Anyway, I had suspected something along these lines, judging from the
behavior of the Evaluate Formula tool, which evaluated the last call to
ROW() as a single element array. Funny thing is Excel crashed when I
attempted to evaluate INDIRECT, which made me think I reached a bug in
the function.

The reason I had used N() was rather different, it was to commit the
range reference into becoming numbers - such was my understanding
following discussions on OFFSET and INDIRECT in other threads. I still
do not fully understand why &ROW() produced and array in this context,
it was outside the MAX() function, I thought it entirely a scalar
context.

Anyway, the formula is working *thank you*, now my task is to further
experiment and maybe understand what is happening with these elusive
functions. I will write back when I have a more concise question and
access to the internet again.

Until then,

Kostis

 

[vezerid]

Hi Bob,

What's the weather like over there?

Funny you should ask. I am in an island, took time away in order to
write on - guess what - Excel for my future courses. I have limited
access to the internet, which makes every visit to the NG minimalistic
and rather frantic. The weather is disappointing for the more southern
lattitude but this has been a long winter anyway.

Anyway, I had suspected something along these lines, judging from the
behavior of the Evaluate Formula tool, which evaluated the last call to
ROW() as a single element array. Funny thing is Excel crashed when I
attempted to evaluate INDIRECT, which made me think I reached a bug in
the function.

The reason I had used N() was rather different, it was to commit the
range reference into becoming numbers - such was my understanding
following discussions on OFFSET and INDIRECT in other threads. I still
do not fully understand why &ROW() produced and array in this context,
it was outside the MAX() function, I thought it entirely a scalar
context.

Anyway, the formula is working *thank you*, now my task is to further
experiment and maybe understand what is happening with these elusive
functions. I will write back when I have a more concise question and
access to the internet again.

Until then,

Kostis