23 views

Skip to first unread message

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

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

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

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

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

...

...

>=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(),

...

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

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

Search

Clear search

Close search

Google apps

Main menu