I've read up on array formulas on the web, and experimented with
multiplying vector array constant with different lengths and
orientations. Regardless of the order in which they appear in the
expression, if a M-tall array constant is multiplied with a N-wide
array constant, the result is an M-tall, N-wide matrix. The result at
the mth row and nth column is the product of the mth element from the
tall vector and the nth element from the wide vector. How universal
is this? Can this be relied upon to hold in all situations? I got
the vertical vectors using TRANSPOSE().

Things get complicated if I multiply 2-D array constants of differing
dimensions. Does anyone know the rules for this? Does it actually do
anything useful enough to be exploited?

Finally, I noticed that one can specify a target area for the array
multiplication that differs in size from the resulting matrix. If it
is too small along a certain dimension, the matrix simply gets
truncated along that direction. If it is too big, the extra cells are
filled with #N/A. Is there a way to show the boundary of cells over
which an array formula is specified?

Thanks.

Paul wrote:
I've read up on array formulas on the web, and experimented with
multiplying vector array constant with different lengths and
orientations. Regardless of the order in which they appear in the
expression, if a M-tall array constant is multiplied with a N-wide
array constant, the result is an M-tall, N-wide matrix. The result at
the mth row and nth column is the product of the mth element from the
tall vector and the nth element from the wide vector. How universal
is this? Can this be relied upon to hold in all situations?

What do you mean "in all situations"?


Things get complicated if I multiply 2-D array constants of differing
dimensions. Does anyone know the rules for this? Does it actually do
anything useful enough to be exploited?

Matrices do not commute with respect to multiplication. I.e., in general
AxB is not equal to BxA; and in all cases, the product exists only if
the number of columns in the left hand array is equal to the number of
rows in the right hand array. And the resulting array has the same
number of rows as the left hand array and the same number of columns as
the right hand array.


Finally, I noticed that one can specify a target area for the array
multiplication that differs in size from the resulting matrix. If it
is too small along a certain dimension, the matrix simply gets
truncated along that direction. If it is too big, the extra cells are
filled with #N/A. Is there a way to show the boundary of cells over
which an array formula is specified?

What do you mean "show the boundary of cells over which an array formula
is specified"? More particularly, what do you mean by "show", what do
you mean by "boundary of cells", and what do you mean by "over which an
array formula is specified"?

Alan Beban

On Mar 20, 9:56 pm, Alan Beban wrote:
Paul wrote:
I've read up on array formulas on the web, and experimented
with multiplying vector array constant with different lengths
and orientations. Regardless of the order in which they
appear in the expression, if a M-tall array constant is
multiplied with a N-wide array constant, the result is an
M-tall, N-wide matrix. The result at the mth row and nth
column is the product of the mth element from the tall vector
and the nth element from the wide vector. How universal is
this? Can this be relied upon to hold in all situations?

What do you mean "in all situations"?

I mean whenever I use an expression involving multiplication
between arrays in Excel, will this be the behaviour? Right now,
it is just an observation based on limited experimenting with
Excel. The behaviour doesn't seem to be documented.

Things get complicated if I multiply 2-D array constants of
differing dimensions. Does anyone know the rules for this?
Does it actually do anything useful enough to be exploited?

Matrices do not commute with respect to multiplication. I.e.,
in general AxB is not equal to BxA; and in all cases, the
product exists only if the number of columns in the left hand
array is equal to the number of rows in the right hand array.
And the resulting array has the same number of rows as the left
hand array and the same number of columns as the right hand
array.

I agree. I am referring specifically to what Excel when an array
formula has two arrays being multiplied.

Finally, I noticed that one can specify a target area for the
array multiplication that differs in size from the resulting
matrix. If it is too small along a certain dimension, the
matrix simply gets truncated along that direction. If it is
too big, the extra cells are filled with #N/A. Is there a
way to show the boundary of cells over which an array formula
is specified?

What do you mean "show the boundary of cells over which an
array formula is specified"? More particularly, what do you
mean by "show", what do you mean by "boundary of cells", and
what do you mean by "over which an array formula is specified"?

An array formula is applied over a rectangular collection of
cells on the spreadsheet. It would be handy to see at a glance
which cells are included in an array formula. I was looking for
a visual indicator (besided clicking on each cell to see if that
particular cell is part of the array formula).