Formula Help – Re-Post (Excel 97 SR2)

Topic

New Reply

Here is my re-post with attachment. I see the attachment file name in the “attach a file” window, so if it isn’t attached this time, I must be doing something wrong and would appreciate advice.

Attached is an example of a sum problem in a worksheet. Except of course there are dozens of rows and columns in the actual worksheet. How can I write a formula as shown, and be able to add and delete rows and still keep the formula valid? Thanks.

Reply | Quote

Replies

You can do this using a array formula, multiplying two matrices. e.g. The first matrix is in A1:A4, but as this is a 4 x 1 matrix, you have to transpose is to a 1 x 4 matrix before you can multiply it with the 4 x 1 matrix in C1:C4. The result will be a 1 x 1 matrix or a simple scalar. Like this

={MMULT(TRANSPOSE(A1:A4);C1:C4)}

which you have to enter as a array formula (that’s indicated by the {}).
I hope I guessed the English formula expressions rightly for matrix multiplication and transposing of a matrix. Change the semicolon into a comma if your regional settings are English.

Reply | Quote

It works! Thanks very much for your help.

Reply | Quote

Your attachment worked fine this time.

Two things:
1. Attachments mostly get lost when you preview after attaching.
2. You can edit a post and attach a file after you’ve posted.

Reply | Quote

Actually, there is another way to do it. I must have thought of it in the first place. That method will also solve problems with empty cells (they will be treated as zero), which cause the #VALUE problem with the previous solution. Therefore, you should use the SUMPRODUCT worksheetfunction (Again, I hope I use the right English for this buitl-in function). This function needs 2 or 3 matrices as arguments.

Reply | Quote

Hi Hans,

<>

Have you seen my xlMenufundict.zip at http://www.bmsltd.ie/mvp%5B/url%5D? it contains translations of (most) worksheetfunctions.

Edited Mar 13th 2004 to update link

Reply | Quote

What I believe Hans is suggesting is filling across with this formula:

=SUMPRODUCT($A$1:$A$4,C1:C4)

Reply | Quote

The SumProduct function solves my problem! Thanks to everyone for their helpful comments.

Reply | Quote