Array formula question

23 views
Skip to first unread message

Ron Rosenfeld

unread,
Mar 30, 2002, 9:45:52 PM3/30/02
to
I'm not understanding something about array formulas.

Consider the following array-entered formula:

=INDIRECT(ADDRESS(ROW(),LARGE(COLUMN(A3:M3)*ISNUMBER(A3:M3),{1,2,3,4}))))

If there are numbers in some of the cells in the range A3:M3, this formula
seems to return an array of the numbers that are contained in the highest
four numbered (or lettered with Z>A) columns.

I would have thought I should be able to apply an arithmetic function to
this array, such as SUM or AVERAGE; but when I do so, it only acts on the
first element of the array, and not on all of the array.

Certainly, if I enter an array constant into the SUM function, such as
=SUM({7, 3, 1, 10}), the entire array will be SUMmed to give 21 as a
result.

But if I have, for example, the following data:

A1 6
C3 10
F3 1
G3 3
I3 7

The above formula, when array-entered into O3:R3, returns 7, 3, 1, 10

But whenever I try to apply a function, it only seems to apply to the first
entry in the array if the array formula is in a single cell; or to the
actual result if the array formula is applied to the O3:R3 range.

I have figured out another method to solve my problem -- that is not the
issue.

I am trying to understand why the above is not working as I thought it
might.

Thanks for any insight.

--ron

Peo Sjoblom

unread,
Mar 30, 2002, 11:17:48 PM3/30/02
to
Hi Ron,

I applied your formula to some dummy numbers and selected from the address
part I got

=SUM(INDIRECT({"$M$3","$L$3","$K$3","$J$3"}))

which would return only the value from M3, if I select from the indirect
part and press F9
I get

=SUM({26,24,22,20})

and if I then ctrl+shift&enter it I get the correct value, so it seems that
it never
acts on the whole array, however if you add transpose it seems to work..


=SUM(TRANSPOSE(INDIRECT(ADDRESS(ROW(),LARGE(COLUMN(A3:M3)*ISNUMBER(A3:M3),{1
,2,3,4})))))


why does it work with transpose??


--
Regards,

Peo Sjoblom

ROT email

"Ron Rosenfeld" <ronros...@spamcop.net> wrote in message
news:h2ucauonb63a35jsu...@4ax.com...

akyurek

unread,
Mar 31, 2002, 4:35:24 AM3/31/02
to
Hi Peo & Ron,

>
=SUM(TRANSPOSE(INDIRECT(ADDRESS(ROW(),LARGE(COLUMN(A3:M3)*ISNUMBER(A3:M3),{1
> ,2,3,4})))))

> why does it work with transpose??

A while ago, Harlan posted this issue in a kindred context. Excel needs a
second round of evaluation or, as Longre calls it to deference the array
elements that

INDIRECT(ADDRESS(ROW(),LARGE(COLUMN(A3:M3)*ISNUMBER(A3:M3),{1,2,3,4})))

computes. TRANSPOSE (or N or a second SUM as I expect) forces the
deferencing or second round of evaluation.

Regards,

Aladin

Peo Sjoblom <gre...@lnubb.pbz> wrote in message
news:ebzRTrG2BHA.2692@tkmsftngp05...

Harlan Grove

unread,
Mar 31, 2002, 4:49:56 AM3/31/02
to
"Peo Sjoblom" <gre...@lnubb.pbz> wrote...
...

>=SUM(INDIRECT({"$M$3","$L$3","$K$3","$J$3"}))
...

>
>which would return only the value from M3, if I select from the indirect
>part and press F9 I get
>
>=SUM({26,24,22,20})
>
>and if I then ctrl+shift&enter it I get the correct value, so it seems that
>it never acts on the whole array, however if you add transpose it seems
>to work..
>
>
>=SUM(TRANSPOSE(INDIRECT(ADDRESS(ROW(),
>LARGE(COLUMN(A3:M3)*ISNUMBER(A3:M3),{1,2,3,4})))))
...

>why does it work with transpose??
> "Ron Rosenfeld" <ronros...@spamcop.net> wrote in message
...

>>Consider the following array-entered formula:
>>
>>=INDIRECT(ADDRESS(ROW(),LARGE(COLUMN(A3:M3)
>>*ISNUMBER(A3:M3),{1,2,3,4}))))
>>
>>If there are numbers in some of the cells in the range A3:M3, this formula
>>seems to return an array of the numbers that are contained in the highest
>>four numbered (or lettered with Z>A) columns.
>>
>>I would have thought I should be able to apply an arithmetic function to
>>this array, such as SUM or AVERAGE; but when I do so, it only acts on the
>>first element of the array, and not on all of the array.
...

This is the undocumented dark side of worksheet formula data types. It seems
that INDIRECT always returns references to ranges. When it's argument is an
array of text addresses, it returns an array of references. An array of
references is NOT the same as an array of values. You can see the same thing
with, for example,

=INDEX(SomeRange,{1;2;3},5)

When you pass an array of references to most functions then to an
aggregating sunction (such as SUM(TRANSPOSE(...)) in this case), the inner
function converts the references to values, and the outer function uses
those values to generate a *single* return value. Try entering
=TRANSPOSE(INDIRECT({"A1";"A2";"A3"})) as an array into C1:E1 to see the
problem. Note, however, the results you get entering
=SUM(INDIRECT({"A1";"A2";"A3"})) into C1:C3.

This comes up from time to time. Pass INDIRECT, INDEX or OFFSET array
arguments and you can get screwy results. This also illustrates that the
[F9] formula bar expression evaluation code isn't the same code as that used
to convert the formula into bytecodes. Silly of us to expect them to give
consistent results.


Ron Rosenfeld

unread,
Mar 31, 2002, 6:56:38 AM3/31/02
to
On Sun, 31 Mar 2002 01:49:56 -0800, "Harlan Grove" <hrl...@aol.com> wrote:

>This is the undocumented dark side of worksheet formula data types. It seems
>that INDIRECT always returns references to ranges. When it's argument is an
>array of text addresses, it returns an array of references. An array of
>references is NOT the same as an array of values.


Thank you Harlan, Peo & Aladin,

Comprehension is slowly dawning :-)

It's interesting, though, that if the reference to the addresses is
contiguous, then the formula works as expected.

For example:


=SUM(INDIRECT("$A$3:"&ADDRESS(3,LARGE(ISNUMBER(A3:M3)*COLUMN(A3:M3),1))))

evaluates as I would expect. And I have some spreadsheets where I use that
sort of construct.

But this is the first time I wound up trying to construct an array of
non-contiguous cells.

Thanks to all of you for your help, and especially to Harlan for your lucid
explanation.

Best wishes,


--ron

Reply all
Reply to author
Forward
0 new messages