Array formula question

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

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...

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...

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.

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