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