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

Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.