Multiplying Arrays

[Phil H]

I understand that it is possible to multiply two arrays using SUMPRODUCT().
How can this be achieved if the orientation of the two arrays is different.
For example, if three cells in a horizintal row with values 10% : 50% and 40%
are intended to be mutiplied in turn by three cells in a vertical column with
values of 3% : 4% and 5%.

When I try this I get the answer 12% and Excel will not allow the use of
TRANSPOSE() to change the orientation of one of the arrays within the
SUMPRODUCT.

Thanks,

 

[Peo Sjoblom]

Try


=SUMPRODUCT(G2:G4,TRANSPOSE(H1:J1))


entered with ctrl + shift & enter

--


Regards,


Peo Sjoblom

 

[Phil H]

Peo,

Thanks v much. Is it possible to achieve the same result without using an
array formula as the requirement to achieve this is part of a much wider
formula that does not need to be entered as an array formula.

Thanks.

 

[Peo Sjoblom]

Nice Dana

--


Regards,


Peo Sjoblom

=MMULT(Horizontal, Vertical)

 

[Harlan Grove]

=MMULT(Horizontal, Vertical)

....

Note that the result is a degenerate 0-D array, so can in obscure
situations cause problems when used as a term in longer formulas
unless coerced to a true scalar via SUM(MMULT(h,v)).

 

[Phil H]

Thanks very much.

=MMULT(Horizontal, Vertical)

 

[Phil H]

Is there any chance that you could explain in this in something approaching
laymans english (i.e. that I might understand).

 

[Harlan Grove]

Is there any chance that you could explain in this in something
approaching laymans english (i.e. that I might understand).

....

Not really because the issues are inherently complex.

MMULT, MINVERSE, TRANSPOSE, ROW, COLUMN, N, T, and INDEX functions can
return single-item arrays, e.g., type the formula =ROW(A1) and press
[F9] rather than [Enter], and Excel will display ={1} rather than 1 in
the formula bar. The good news for MINVERSE, TRANSPOSE, N, T and INDEX
is that they only return single-item arrays when passed single-item
arrays. MMULT, ROW and COLUMN, OTOH, can return single-item arrays
when you wouldn't necessarily expect them to do so.

Problems arise when you use dynamic ranges, i.e., references to ranges
produced by OFFSET and INDIRECT functions, where any of the 2nd
through 5th arguments to OFFSET or the one and only argument to
INDIRECT are arrays. In those situations, OFFSET and INDIRECT return
undocumented 'objects' that can only be described as arrays of range
references. The only functions that can use those beasts are SUMIF,
COUNTIF and presumably the Excel 2007/2008 extensions AVERAGEIF,
SUMIFS, COUNTIFS and AVERAGEIFS. All other functions return error
values when passed these 'objects'.