Measuring Average Changes

[seahorses09]

So here is what I'm looking at...

December - 100 - N/A
January - 200 - 100% change (1.0)
February - 300 - 50% change (.5)
March - 100 - 66% change (.66)

I can tell what the percentage change is from month to month, but I'm
not sure what the best way is to figure out the average percentage
change, overall. I can't add up the decimal versions of each
percentage difference and then divide by the number of months to get
some kind of overall average, right? In this case, that would turn
out to be an average variance of 72%.

Please help - thanks!

 

[joeu2004]

So here is what I'm looking at...

December - 100 - N/A
January - 200 - 100% change (1.0)
February - 300 - 50% change (.5)
March - 100 - 66% change (.66)

I can tell what the percentage change is from month to month, but I'm
not sure what the best way is to figure out the average percentage
change, overall. I can't add up the decimal versions of each
percentage difference and then divide by the number of months to get
some kind of overall average, right? In this case, that would turn
out to be an average variance of 72%.

Please help - thanks!

 

[Guest]

This looks like a math question rather than excel question. I guess it also
depends on what you are lookig for. One suggestion is use the "geometric
average". [((Final Value/Initial Value)^ (1/nr of periods))-1].
In your case it is (100/100)^(1/3)-1=0
I hope this helps

 

[joeu2004]

So here is what I'm looking at...
December - 100 - N/A
January - 200 - 100% change (1.0)
February - 300 - 50% change (.5)
March - 100 - 66% change (.66)

I can tell what the percentage change is from month to month, but I'm
not sure what the best way is to figure out the average percentage
change, overall. I can't add up the decimal versions of each
percentage difference and then divide by the number of months to get
some kind of overall average, right? In this case, that would turn
out to be an average variance of 72%.

Actually, 28%. The last change -- "66%" -- is really -67%.

The average of the percentages __might__ make sense in some contexts.
It answers the question: "what is the expected percentage change
month-to-month?". That is indeed the average of the percentages.

But I suspect you are more interested in the geometric mean -- that
is, the net change over time. In the example above, you went from 100
to 100 over 3 months. You expect a net change of 0%. Right?

One way to compute that is the following array formula (commit with
ctrl-shift-Enter, not Enter):

=geomean(1+B2:B4) - 1

Alternatively, the simple formula (not an array formula):

=(B4/B1)^(1/3) - 1

Be sure to format the cell as Percentage, if that is what you want to
see.

 

[seahorses09]

Actually, 28%. The last change -- "66%" -- is really -67%.

The average of the percentages __might__ make sense in some contexts.
It answers the question: "what is the expected percentage change
month-to-month?". That is indeed the average of the percentages.

But I suspect you are more interested in the geometric mean -- that
is, the net change over time. In the example above, you went from 100
to 100 over 3 months. You expect a net change of 0%. Right?

One way to compute that is the following array formula (commit with
ctrl-shift-Enter, not Enter):

=geomean(1+B2:B4) - 1

Alternatively, the simple formula (not an array formula):

=(B4/B1)^(1/3) - 1

Be sure to format the cell as Percentage, if that is what you want to
see.

Thanks for that explanation. What operation does the ^ represent in
Excel?

 

[joeu2004]

Alternatively, the simple formula (not an array formula):
=(B4/B1)^(1/3) - 1

Thanks for that explanation. What operation does the ^ represent in
Excel

I am so glad you asked, first because it is always good to ask ("there
are no dumb questions"), and second because it explains why I prefer
to give people solutions that use Excel functions instead of
exponential formulas, which others insist are "better" because they
more efficient (and they are right).

Anyway, to answer your question: in this context, "^" (called caret
or circumflex) is an arithmetic operator that means "raise to the
power of". That is, "y^n" means "y raised to the power of n", which
means multiply y times itself n times.

Your next question might be: "what the heck does it mean to raise y
to the 1/3 power; that is, to multiply y times itself 1/3 times
(huh!!)?".

That notation means: take the n-th root -- the "third" or cube root,
in this case. We are trying find the percent change which, when
applied 3 times to successive results starting with the first number
or present value (first 100), will result in the last number or future
value (last 100).

Of course, in this case, the answer is quite trivial: 0%. For a more
interesting result, change the last 100 to 150 for example.

HTH.