I am familiar with the sumproduct function when calculating a range of data
but I was wondering if I could sum divide. I have following example:

Column A is lifetime of an object, say 1.5 years
Column B is the value of that product, say $10,000
I want to divide $10,000 by the 1.5 years so I will know the annual "cost"
of the good. I want to determine this for a list of different lifetimes and
values to determine the annual "cost" of the equipmnet.

I was wondering is there a quick way to do this or should I just add a
column for the division function and sum that column?

Thanks

Dale

Just create a matrix with value running along the horizontal axis and life
running along the vertical, and divide the x,y values at each intersection.

Then you have a matrix of various annual costs for various life times.

I don't really think you can think of your "sum divide" as the inverse of
sumproduct.

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"Dale" wrote:

I am familiar with the sumproduct function when calculating a range of data
but I was wondering if I could sum divide. I have following example:

Column A is lifetime of an object, say 1.5 years
Column B is the value of that product, say $10,000
I want to divide $10,000 by the 1.5 years so I will know the annual "cost"
of the good. I want to determine this for a list of different lifetimes and
values to determine the annual "cost" of the equipmnet.

I was wondering is there a quick way to do this or should I just add a
column for the division function and sum that column?

Thanks

Dale

sumproduct still works - use the division operator between ranges:
=SUMPRODUCT((A2:A10)/(B2:B10))


"Dale" wrote:

I am familiar with the sumproduct function when calculating a range of data
but I was wondering if I could sum divide. I have following example:

Column A is lifetime of an object, say 1.5 years
Column B is the value of that product, say $10,000
I want to divide $10,000 by the 1.5 years so I will know the annual "cost"
of the good. I want to determine this for a list of different lifetimes and
values to determine the annual "cost" of the equipmnet.

I was wondering is there a quick way to do this or should I just add a
column for the division function and sum that column?

Thanks

Dale

Wow! I didn't know that. After reading your post I found I could
"SumExponentiate" also -- cool!



"BoniM" wrote:

sumproduct still works - use the division operator between ranges:
=SUMPRODUCT((A2:A10)/(B2:B10))


"Dale" wrote:

I am familiar with the sumproduct function when calculating a range of data
but I was wondering if I could sum divide. I have following example:

Column A is lifetime of an object, say 1.5 years
Column B is the value of that product, say $10,000
I want to divide $10,000 by the 1.5 years so I will know the annual "cost"
of the good. I want to determine this for a list of different lifetimes and
values to determine the annual "cost" of the equipmnet.

I was wondering is there a quick way to do this or should I just add a
column for the division function and sum that column?

Thanks

Dale

Of course, for your sample, you would be dividing column b by column a - my
sample showed the reverse.

"Dale" wrote:

I am familiar with the sumproduct function when calculating a range of data
but I was wondering if I could sum divide. I have following example:

Column A is lifetime of an object, say 1.5 years
Column B is the value of that product, say $10,000
I want to divide $10,000 by the 1.5 years so I will know the annual "cost"
of the good. I want to determine this for a list of different lifetimes and
values to determine the annual "cost" of the equipmnet.

I was wondering is there a quick way to do this or should I just add a
column for the division function and sum that column?

Thanks

Dale

Thanks for the help BoniM. It works great.

Dale

"BoniM" wrote:

Of course, for your sample, you would be dividing column b by column a - my
sample showed the reverse.

"Dale" wrote:

I am familiar with the sumproduct function when calculating a range of data
but I was wondering if I could sum divide. I have following example:

Column A is lifetime of an object, say 1.5 years
Column B is the value of that product, say $10,000
I want to divide $10,000 by the 1.5 years so I will know the annual "cost"
of the good. I want to determine this for a list of different lifetimes and
values to determine the annual "cost" of the equipmnet.

I was wondering is there a quick way to do this or should I just add a
column for the division function and sum that column?

Thanks

Dale

Hi Dale,

Yes, you can definitely use a formula to divide the value by the lifetime of the object to get the annual cost. Instead of using the SUMPRODUCT function, you can use the SUM function along with the DIVIDE function (which is represented by the forward slash "/").

Here's an example formula you can use:

1. =SUM(B2:B10/D2:D10)

In this formula, B2:B10 represents the range of values and D2:D10 represents the range of lifetimes. The formula will divide each value in column B by its corresponding lifetime in column D, and then sum up the results to give you the total annual cost of the equipment.

You can adjust the ranges in the formula to match the actual range of data in your worksheet.

Note: If there is any macro or Visual Basic code, wrap it in the tags.