Consulting

Results 1 to 8 of 8

Thread: Sum divide

  1. #1

    Sum divide

    hi,
    i've just seen the sumproduct tutorial. Does excel has a "sum divide" function?
    Can you show me the weblink to this tutorial, thanks?

  2. #2
    Mac Moderator VBAX Guru mikerickson's Avatar
    Joined
    May 2007
    Location
    Davis CA
    Posts
    2,778
    Do you mean something like
    =SUMPRODUCT(1/(A1:A10), 1/(B1:B10))
    Anything like that has #DIV/0 problems, that can be worked around.

    Do you have a specific need for a SumDivide?

  3. #3
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Or maybe you mean

    =SUMPRODUCT(A1:A1/B1:B5)
    Perhaps you could clarify what you mean.

    As to a weblink, to what tutorial?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  4. #4
    hi,

    example:

    row colA colB
    1 2 10
    2 1.3
    3 2.5 30
    -----------------
    Answer: 17
    -----------------

    i need to find out the sum of these 3 rows.

    17 = (10/2) + (1.3/0) + (30/2.5)

    I have many rows of data. If i were to write the formula like above, it would takes up plenty of time!
    i do not know whether excel have such a formula like sumdivide to perform the above functions.

  5. #5
    Mac Moderator VBAX Guru mikerickson's Avatar
    Joined
    May 2007
    Location
    Davis CA
    Posts
    2,778
    17 does not equal (10/2) + (1.3/0) + (30/2.5).

    The second term is undefined and, therefore, the sum is also.

    But a variation of xld's formula might work.

    perhaps

    =SUMPRODUCT(B1:B3/A1:A3)

  6. #6
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    No, that will give an error as well. I would suggest this array formula

    =SUM(IF(NOT(ISERROR(B1:B3/A1:A3)),B1:B3/A1:A3))
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  7. #7
    it works great, thanks

  8. #8
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    There are 3 excellent SumProduct tutorials as 'Stickies' are the top of the SumProduct sub-forum that have a very good presentation of how to use Sumproduct


    http://www.vbaexpress.com/forum/forumdisplay.php?f=98

    As well as

    http://xldynamic.com/source/xld.SUMPRODUCT.html

    Paul

Posting Permissions

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