PDA

View Full Version : Solved: sum divide

benong
01-01-2011, 09:27 PM
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?

mikerickson
01-02-2011, 11:28 PM
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?

Bob Phillips
01-03-2011, 03:23 PM
Or maybe you mean

=SUMPRODUCT(A1:A1/B1:B5)

Perhaps you could clarify what you mean.

As to a weblink, to what tutorial?

benong
01-04-2011, 05:27 PM
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.

mikerickson
01-04-2011, 06:13 PM
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)

Bob Phillips
01-05-2011, 03:56 AM
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))

benong
01-09-2011, 06:01 PM
it works great, thanks :)

Paul_Hossler
01-16-2011, 11:26 AM
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