Monthly Percent Increase

[pivot table]

Say I have the data listed below:

January - 250
February -275
March - 350
April - 400
May - 500
June - 650
July - 1000
August -1250
September -1600
October - 2000
November - 2600
December - 2750

Here is the formula I used:

=(February/January)-1

Once all the months are done then average all the percents together to get a
monthly percent increase average...

I want to get monthly percent increase then get an average monthly percent
increase of all months.

Please advise the easiest way to do this and what the formula would be.

Thanks,
nolanstern

 

[joeu2004]

Please note that there are two kinds of averages that you might have
in mind, both equally valid depending on your purpose.

If you are interested in the "typical" monthly rate of change, you
would simply use AVERAGE(A2:A12), where A2 is the Feb/Jan-1 rate, A3
is Mar/Feb-1 rate, etc. Of course, that does not account for other
factors, such as seasonal influences.

If you are interested in the average compounded rate -- the average
rate that gets you from the Jan amount to the Dec amount when
compounded -- there are many ways to do that.

Perhaps the simplest way is: (Dec/Jan)^(1/11) - 1.

Another way is the following array formulas (commit with ctrl-shift-
Enter instead of just Enter): GEOMEAN(A2:A12+1) - 1.

But beware that GEOMEAN and PRODUCT have poor numerical properties if
there are a large number of months. Arguably, it is better to do
those computations using logarithms (gulp!). For example, with this
regular formula (commit with Enter as usual):

10^(SUMPRODUCT(LOG(A2:A12+1)) / 11) - 1

Note that the "11" is the number of items in the range A2:A12, whereas
the "10" is constant.


----- original posting -----