Results 1 to 8 of 8

20011019, 17:24 #1
 Join Date
 Jan 2001
 Posts
 18
 Thanks
 0
 Thanked 0 Times in 0 Posts
Formula Help  RePost (Excel 97 SR2)
Here is my repost 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.

20011019, 17:47 #2
 Join Date
 Jan 2001
 Location
 Kortrijk, Belgium
 Posts
 571
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Formula Help  RePost (Excel 97 SR2)
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.

20011019, 18:49 #3
 Join Date
 Jan 2001
 Posts
 18
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Formula Help  RePost (Excel 97 SR2)
It works! Thanks very much for your help.

20011019, 21:22 #4
 Join Date
 Dec 2000
 Location
 Queanbeyan, New South Wales, Australia
 Posts
 3,730
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Formula Help  RePost (Excel 97 SR2)
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.Subway Belconnen home of the Signboard to make you smile. Get (almost) daily updates follow SubwayBelconnen on Twitter.

20011020, 06:23 #5
 Join Date
 Jan 2001
 Location
 Kortrijk, Belgium
 Posts
 571
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Formula Help  RePost (Excel 97 SR2)
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 buitlin function). This function needs 2 or 3 matrices as arguments.

20011020, 23:16 #6
 Join Date
 Jan 2001
 Location
 La Jolla, CA
 Posts
 1,509
 Thanks
 34
 Thanked 66 Times in 62 Posts
Re: Formula Help  RePost (Excel 97 SR2)
What I believe Hans is suggesting is filling across with this formula:
=SUMPRODUCT($A$1:$A$4,C1:C4)

20011022, 15:20 #7
 Join Date
 Jan 2001
 Posts
 18
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Formula Help  RePost (Excel 97 SR2)
The SumProduct function solves my problem! Thanks to everyone for their helpful comments.

20040313, 16:44 #8
 Join Date
 Feb 2001
 Location
 Weert, Limburg, Netherlands
 Posts
 4,812
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Formula Help  RePost (Excel 97 SR2)
Hi Hans,
<<I hope I use the right English for...>>
Have you seen my xlMenufundict.zip at http://www.bmsltd.ie/mvp? it contains translations of (most) worksheetfunctions.
Edited Mar 13th 2004 to update linkJan Karel Pieterse
Microsoft Excel MVP, WMVP
www.jkpads.com
Professional Office Developers Association