Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Weighted Average of positive and negative %

I have the following numbers and corresponding growth rates:

A B
1 4197 7.90%
2 4114 8.30%
3 1885 0.50%
4 624 10.50%
5 1051 -3.30%
6 216 16.20%

My problem is the negative growth rate of -3.30% - I want to calculate
the weighted average growth rate and get the following results for the
respective formulas:

=SUMPRODUCT(A1:A6,B1:B6)/SUM(A1:A6) = 6.2%

=SUMPRODUCT(A1:A6,ABS(B1:B6))/SUM(A1:A6) = 6.8%


Do I need the ABS() function or not? Which would be the correct result?


Thanks!
JK

  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up Answer: Weighted Average of positive and negative %

Hello JK,

To calculate the weighted average growth rate, you can use the formula:

Code:
=SUMPRODUCT(A1:A6,B1:B6)/SUM(A1:A6)
This formula multiplies each number in column A by its corresponding growth rate in column B, adds up the results, and then divides by the total sum of column A.

However, since you have a negative growth rate in cell B5, you may want to consider using the ABS() function to get the absolute value of the growth rate. This is because the negative growth rate will decrease the overall weighted average growth rate.

To calculate the weighted average growth rate with the absolute value of the growth rates, you can use the formula:

Code:
=SUMPRODUCT(A1:A6,ABS(B1:B6))/SUM(A1:A6)
This formula multiplies each number in column A by the absolute value of its corresponding growth rate in column B, adds up the results, and then divides by the total sum of column A.

So, to answer your question, if you want to include the negative growth rate in your calculation, you do not need to use the ABS() function. However, if you want to calculate the weighted average growth rate without the negative growth rate affecting the result, you should use the ABS() function.
  1. Use the formula
    Code:
    =SUMPRODUCT(A1:A6,B1:B6)/SUM(A1:A6)
    to calculate the weighted average growth rate.
  2. Consider using the ABS() function if you have negative growth rates.
  3. Use the formula
    Code:
    =SUMPRODUCT(A1:A6,ABS(B1:B6))/SUM(A1:A6)
    to calculate the weighted average growth rate with the absolute value of the growth rates.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,574
Default Weighted Average of positive and negative %

If you use the absolute value of -3.3% then you are essentially implying a
growth rate of POSITIVE 3.3%. If that's your intent, then use the absolute
value. Else, use the relative values shown.

I'm not sure why you would want to use absolute values for a weighted
average when some of your data shows negative growth rates. Also, as for the
issue of weighting rates--typically, average rates are calculated with the
harmonic mean, not the arithmetic mean:
http://en.wikipedia.org/wiki/Harmonic_mean

Dave



Dave
--
Brevity is the soul of wit.


" wrote:

I have the following numbers and corresponding growth rates:

A B
1 4197 7.90%
2 4114 8.30%
3 1885 0.50%
4 624 10.50%
5 1051 -3.30%
6 216 16.20%

My problem is the negative growth rate of -3.30% - I want to calculate
the weighted average growth rate and get the following results for the
respective formulas:

=SUMPRODUCT(A1:A6,B1:B6)/SUM(A1:A6) = 6.2%

=SUMPRODUCT(A1:A6,ABS(B1:B6))/SUM(A1:A6) = 6.8%


Do I need the ABS() function or not? Which would be the correct result?


Thanks!
JK


  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
JK JK is offline
external usenet poster
 
Posts: 1
Default Weighted Average of positive and negative %

Thanks for the answer - this helps a lot. So how would I calculate the
weighted harmonic mean of the data set above?

Dave F wrote:
If you use the absolute value of -3.3% then you are essentially implying a
growth rate of POSITIVE 3.3%. If that's your intent, then use the absolute
value. Else, use the relative values shown.

I'm not sure why you would want to use absolute values for a weighted
average when some of your data shows negative growth rates. Also, as for the
issue of weighting rates--typically, average rates are calculated with the
harmonic mean, not the arithmetic mean:
http://en.wikipedia.org/wiki/Harmonic_mean

Dave



Dave
--
Brevity is the soul of wit.


" wrote:

I have the following numbers and corresponding growth rates:

A B
1 4197 7.90%
2 4114 8.30%
3 1885 0.50%
4 624 10.50%
5 1051 -3.30%
6 216 16.20%

My problem is the negative growth rate of -3.30% - I want to calculate
the weighted average growth rate and get the following results for the
respective formulas:

=SUMPRODUCT(A1:A6,B1:B6)/SUM(A1:A6) = 6.2%

=SUMPRODUCT(A1:A6,ABS(B1:B6))/SUM(A1:A6) = 6.8%


Do I need the ABS() function or not? Which would be the correct result?


Thanks!
JK



  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 837
Default Weighted Average of positive and negative %

=SUM(weights)/SUMPRODUCT(weights,1/data)

This will return #DIV/0 if even one observation in data is zero. If a
single observation is very close to zero, that observation will tend to
dominate all of the other data in the harmonic mean. Are you sure that is
appropriate?

Jerry

"JK" wrote:

Thanks for the answer - this helps a lot. So how would I calculate the
weighted harmonic mean of the data set above?

Dave F wrote:
If you use the absolute value of -3.3% then you are essentially implying a
growth rate of POSITIVE 3.3%. If that's your intent, then use the absolute
value. Else, use the relative values shown.

I'm not sure why you would want to use absolute values for a weighted
average when some of your data shows negative growth rates. Also, as for the
issue of weighting rates--typically, average rates are calculated with the
harmonic mean, not the arithmetic mean:
http://en.wikipedia.org/wiki/Harmonic_mean

Dave



Dave
--
Brevity is the soul of wit.


" wrote:

I have the following numbers and corresponding growth rates:

A B
1 4197 7.90%
2 4114 8.30%
3 1885 0.50%
4 624 10.50%
5 1051 -3.30%
6 216 16.20%

My problem is the negative growth rate of -3.30% - I want to calculate
the weighted average growth rate and get the following results for the
respective formulas:

=SUMPRODUCT(A1:A6,B1:B6)/SUM(A1:A6) = 6.2%

=SUMPRODUCT(A1:A6,ABS(B1:B6))/SUM(A1:A6) = 6.8%


Do I need the ABS() function or not? Which would be the correct result?


Thanks!
JK






  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,574
Default Weighted Average of positive and negative %

Well, when averaging rates, it's appropriate to use the harmonic mean. My
guess is one would throw out rates of 0.00% when calculating such, for the
reasons you cite. It should also be noted that 0 values tend to affect
arithmetic averages as well.

You're correct that a weighted average is different than a straight
arithmetic mean.

Perhaps the best answer to the OP's question is to do what he originally
did, however, he should be using absolute values but rather the original
values.
--
Brevity is the soul of wit.


"Jerry W. Lewis" wrote:

=SUM(weights)/SUMPRODUCT(weights,1/data)

This will return #DIV/0 if even one observation in data is zero. If a
single observation is very close to zero, that observation will tend to
dominate all of the other data in the harmonic mean. Are you sure that is
appropriate?

Jerry

"JK" wrote:

Thanks for the answer - this helps a lot. So how would I calculate the
weighted harmonic mean of the data set above?

Dave F wrote:
If you use the absolute value of -3.3% then you are essentially implying a
growth rate of POSITIVE 3.3%. If that's your intent, then use the absolute
value. Else, use the relative values shown.

I'm not sure why you would want to use absolute values for a weighted
average when some of your data shows negative growth rates. Also, as for the
issue of weighting rates--typically, average rates are calculated with the
harmonic mean, not the arithmetic mean:
http://en.wikipedia.org/wiki/Harmonic_mean

Dave



Dave
--
Brevity is the soul of wit.


" wrote:

I have the following numbers and corresponding growth rates:

A B
1 4197 7.90%
2 4114 8.30%
3 1885 0.50%
4 624 10.50%
5 1051 -3.30%
6 216 16.20%

My problem is the negative growth rate of -3.30% - I want to calculate
the weighted average growth rate and get the following results for the
respective formulas:

=SUMPRODUCT(A1:A6,B1:B6)/SUM(A1:A6) = 6.2%

=SUMPRODUCT(A1:A6,ABS(B1:B6))/SUM(A1:A6) = 6.8%


Do I need the ABS() function or not? Which would be the correct result?


Thanks!
JK




  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 751
Default Weighted Average of positive and negative %

JK,
Having followed the discussion so far:

First, I think you need a geometric mean and not a harmonic mean. If
you have growth rates in B1:B6 then the geometric mean would be the
following *array* formula:
=PRODUCT(1+B1:B6)^(1/ROWS(B1:B6))-1

More simply, it can also be produced with GEOMEAN (again *array*):
=GEOMEAN(1+B1:B6)-1

Then, if this is x, the end value should be equal to
=StartValue*(1+x)^ROWS(B1:B6)

A variant of this formula could possibly be used for weighted geometric
mean but I am at a loss as to what your "weights" imply. Also I cannot
understand what you mean when you say:
I have the following numbers and corresponding growth rates


If you are tracking a single time period, have different amounts (e.g.
like in a portfolio) and ask what is the yield of the portfolio then
negative numbers should not be a problem. Also in this case there is
no issue for Geometric mean. The yield would be:

=SUMPRODUCT(A1:A6,1+B1:B6)/SUM(A1:A6)-1

Does either suit you?

HTH
Kostis Vezerides

JK wrote:
Thanks for the answer - this helps a lot. So how would I calculate the
weighted harmonic mean of the data set above?

Dave F wrote:
If you use the absolute value of -3.3% then you are essentially implying a
growth rate of POSITIVE 3.3%. If that's your intent, then use the absolute
value. Else, use the relative values shown.

I'm not sure why you would want to use absolute values for a weighted
average when some of your data shows negative growth rates. Also, as for the
issue of weighting rates--typically, average rates are calculated with the
harmonic mean, not the arithmetic mean:
http://en.wikipedia.org/wiki/Harmonic_mean

Dave



Dave
--
Brevity is the soul of wit.


" wrote:

I have the following numbers and corresponding growth rates:

A B
1 4197 7.90%
2 4114 8.30%
3 1885 0.50%
4 624 10.50%
5 1051 -3.30%
6 216 16.20%

My problem is the negative growth rate of -3.30% - I want to calculate
the weighted average growth rate and get the following results for the
respective formulas:

=SUMPRODUCT(A1:A6,B1:B6)/SUM(A1:A6) = 6.2%

=SUMPRODUCT(A1:A6,ABS(B1:B6))/SUM(A1:A6) = 6.8%


Do I need the ABS() function or not? Which would be the correct result?


Thanks!
JK



  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 837
Default Weighted Average of positive and negative %

GEOMEAN can easily overflow with large n or large values or underflow with
small values. A better way to calculate GEOMEAN(data) would be the array
formula
=EXP(AVERAGE(LN(data)))
which leads immediatly to the weighted geometric mean
=EXP(SUMPRODUCT(weights,LN(data))/SUM(weights))

It is not at all clear to me that the linear shift you propose (to deal with
negative values) is appropriate for dealing with this data.

Jerry

"vezerid" wrote:

JK,
Having followed the discussion so far:

First, I think you need a geometric mean and not a harmonic mean. If
you have growth rates in B1:B6 then the geometric mean would be the
following *array* formula:
=PRODUCT(1+B1:B6)^(1/ROWS(B1:B6))-1

More simply, it can also be produced with GEOMEAN (again *array*):
=GEOMEAN(1+B1:B6)-1

Then, if this is x, the end value should be equal to
=StartValue*(1+x)^ROWS(B1:B6)

A variant of this formula could possibly be used for weighted geometric
mean but I am at a loss as to what your "weights" imply. Also I cannot
understand what you mean when you say:
I have the following numbers and corresponding growth rates


If you are tracking a single time period, have different amounts (e.g.
like in a portfolio) and ask what is the yield of the portfolio then
negative numbers should not be a problem. Also in this case there is
no issue for Geometric mean. The yield would be:

=SUMPRODUCT(A1:A6,1+B1:B6)/SUM(A1:A6)-1

Does either suit you?

HTH
Kostis Vezerides

JK wrote:
Thanks for the answer - this helps a lot. So how would I calculate the
weighted harmonic mean of the data set above?

Dave F wrote:
If you use the absolute value of -3.3% then you are essentially implying a
growth rate of POSITIVE 3.3%. If that's your intent, then use the absolute
value. Else, use the relative values shown.

I'm not sure why you would want to use absolute values for a weighted
average when some of your data shows negative growth rates. Also, as for the
issue of weighting rates--typically, average rates are calculated with the
harmonic mean, not the arithmetic mean:
http://en.wikipedia.org/wiki/Harmonic_mean

Dave



Dave
--
Brevity is the soul of wit.


" wrote:

I have the following numbers and corresponding growth rates:

A B
1 4197 7.90%
2 4114 8.30%
3 1885 0.50%
4 624 10.50%
5 1051 -3.30%
6 216 16.20%

My problem is the negative growth rate of -3.30% - I want to calculate
the weighted average growth rate and get the following results for the
respective formulas:

=SUMPRODUCT(A1:A6,B1:B6)/SUM(A1:A6) = 6.2%

=SUMPRODUCT(A1:A6,ABS(B1:B6))/SUM(A1:A6) = 6.8%


Do I need the ABS() function or not? Which would be the correct result?


Thanks!
JK




  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 418
Default Weighted Average of positive and negative %

Jerry W. Lewis wrote:
It is not at all clear to me that the linear shift you propose (to deal with
negative values) is appropriate for dealing with this data.


It is appropriate, but it is not necessary. I had started with "1+g"
for my earlier response out of habit, because that __is__ necessary
when computing the log. But then I realized that SUM(w[i]*g[i]) =
SUM(w[i]*(1+g[i])) - 1.

But all this is academic with respect to the OP's problem because I
believe the OP is not dealing with a time series, but with a collection
of categories. As I explained in my earlier response, we can
demonstrate that in that case, SUM(w[i]*g[i]) is the correct solution
for the OP's problem, and it does matter that some g[i] are negative.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Add sequences of positive then negative numbers judoist Excel Discussion (Misc queries) 6 November 26th 05 06:51 AM
I Need a formula to evaluate a cell with + or - values Bob in Oklahoma Excel Worksheet Functions 6 October 31st 05 03:41 PM
Formula to make Negative Values Positive & Positive Values Negative? mustard Excel Discussion (Misc queries) 4 September 26th 05 10:05 PM
change cell from negative to positive Terry Excel Discussion (Misc queries) 1 July 15th 05 07:15 PM
How can I change positive numbers to negative, i.e. change 50 to - godwingi Excel Discussion (Misc queries) 5 February 28th 05 06:41 PM


All times are GMT +1. The time now is 12:59 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"