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
--
Message posted from http://www.ExcelForum.com

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

On Sat, 7 Aug 2004 17:59:45 -0500, toverstreet
wrote:

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



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

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

The second has the same list of finished parts down the side and months
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 body
to be finished part demand X component qty for that finished part.

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

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

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

Attachment filename: test.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=641884
---
Message posted from http://www.ExcelForum.com/

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

On Sat, 07 Aug 2004 19:28:34 -0500, Myrna Larson
wrote:

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

On Sat, 7 Aug 2004 17:59:45 -0500, toverstreet
wrote:

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



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

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

The second has the same list of finished parts down the side and months
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 body
to be finished part demand X component qty for that finished part.

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

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

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

Attachment filename: test.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=641884
---
Message posted from http://www.ExcelForum.com/

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!