Array multiplication?

[toverstreet]

I just posted to formuals side but I think it might be solved with VB
so I am going to post here also. I apologize for double posting bu
this is a critical issue at the moment.



Hi, I have two tables I am trying to get to multiply against eac
other.

The first has components on one axis and finished product across th
top. The body of the table is the # of components in each finishe
part.

The second has the same list of finished parts down the side and month
across the top. The body is the demand for each part, for each month.

What I need is a table with component and month as axis with the bod
to be finished part demand X component qty for that finished part.

I have attached simplified version showing two tables, and the expecte
answer.

I need some ideas on how to get the table to calculate the answer. (th
actual table is going to be 900rowsX180cols)

Note: I am comfortable with complex formulas and can also write VBA i
you can point me in the right direction

Attachment filename: test.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=64188

 

[Myrna Larson]

With the layout you showed (body of Table 1 starts in B5, body of Table 2
starts in I5), I used this array formula for the Comp1 January result (enter
the formula with CTRL+SHIFT+ENTER, not just ENTER)

=SUM((TRANSPOSE($B5:$D5))*(I$5:I$7))

I copied it right and down. It gave me the results you show.

PS: I was not able to get this to work with

 

[Myrna Larson]

A word is missing. I intended to say that I couldn't get a SUMPRODUCT formula
to work.

 

[Stephen]

Problems of this type are solved through matrix multiplication. Excel
has in-built functions to do this ie "MMULT".

The format is thus, highlight your output range then enter the Excel
function; "=MMULT(Range1,Range2)" followed by CTRL+SHIFT+ENTER. All
being well the output will apear in your output range (I tried it on
your sheet with no problems provided you enter zero's in the empty input
cells). Subsequent changes to your input data automatically updates the
output so once you have set up your sheet there is no need to redo it
each time.

There are a few important rules with matrix algebra, one being that the
two ranges to be multiplied must match each other ie the number of
columns in the first range to be multiplied must match the rows in the
second range if not Excel will return errors.

If you need more help, email me your attachment and I will insert the
formula for you.

StephenS

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!