Multiplying Arrays

[dik]

Multiplying Arrays

Is there a simple method of multiplying arrays?

I have 2 groups of data that I want to multiply and manipulate.

A1 B1
A2 B2
A3 B3
...
An Bn

I've tried declaring A1 and B1 as named variables and I've tried declaring A1:An and B1:Bn also as named variables. Is there a simple manner of multiplying and summing the results? Can this be extended to m x n arrays?

Dik

 

[IRstuff]

Excel has a function called MMULT, have you tried that?

TTFN
faq731-376

 

[dik]

Will take a look at it... didn't know it was...

Dik

 

[electricpete]

Assuming N=10 and you want sum of product of corresponding elements, use this
=MMULT(TRANSPOSE(A1:A10),B1:B10)
but do not press enter after you type the formula, press ctrl-enter instead

=====================================
(2B)+(2B)' ?

 

[electricpete]

press ctrl-enter instead

Sorry, that should be contrl-shift-enter
The previous example responded to your request "Is there a simple manner of multiplying and summing the results?"

If you want to do a matrix operation that results in output larger than one cell, select the correct size output range, enter the formula and again press control-shift-enter

=====================================
(2B)+(2B)' ?

 

[IDS]

ElectricPete's method is the way to do it on the spreadsheet, but it looks like you are talking VBA. You can use the same worksheet functions as follows:

If your data is in two named ranges, Range1 and Range2 and you want the result in named range Range3 then:

Dim Array1 as variant, Array2 as variant, Array3 as variant

Array1 = Range("Range1").Value2
Array2 = Range("Range2").Value2

Array3 = Worksheetfunction.MMult(Array1, Array2)

Range3.Value = Array3

Add in worksheetfunction.transpose(arrayname) as required.
Note that:
- You don't need to dimension Array1, 2 and 3 as arrays, VBA does it automatically when you assign the range values to the Variant
- Using .Value2 rather than .Value discards formatting information, and makes the operation significantly faster.



Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/