Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Jan 2001
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formula Help - Re-Post (Excel 97 SR2)

    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.
    Attached Files Attached Files

  2. #2
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula Help - Re-Post (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.

  3. #3
    New Lounger
    Join Date
    Jan 2001
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula Help - Re-Post (Excel 97 SR2)

    It works! Thanks very much for your help.

  4. #4
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula Help - Re-Post (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.

  5. #5
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula Help - Re-Post (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 buitl-in function). This function needs 2 or 3 matrices as arguments.

  6. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    Re: Formula Help - Re-Post (Excel 97 SR2)

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

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

  7. #7
    New Lounger
    Join Date
    Jan 2001
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula Help - Re-Post (Excel 97 SR2)

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

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula Help - Re-Post (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 link
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •