Rate of change

[Mike]

I have 13 data points by month. I need to calculate the rate of change over
the 13 months. Is calculating the percentage difference from month to month
starting with 03/08 and then average the results the correct way? Below is
the result I get, is this correct or is there an excel function specific for
this?

02/08 38
03/08 39 2.63%
04/08 41 5.13%
05/08 34 -17.07%
06/08 43 26.47%
07/08 39 -9.30%
08/08 38 -2.56%
09/08 32 -15.79%
10/08 38 18.75%
11/08 25 -34.21%
12/08 33 32.00%
01/09 30 -9.09%
02/09 24 -20.00%
03/09 29 20.83%
-0.17% (Average)

Many Thanks,

 

[Gary''s Student]

The best way would be to use a linear fit to the data and then calculate the
average monthly rate of change from the slope.

 

[Mike]

I am not for sure how to do that but would'nt the function Slope give me the
rate of change?

 

[Gary''s Student]

They are related. With your data in A1 thru B14:

1.00 38
2.00 39
3.00 41
4.00 34
5.00 43
6.00 39
7.00 38
8.00 32
9.00 38
10.00 25
11.00 33
12.00 30
13.00 24
14.00 29


then in G1 & G2:

=SLOPE(B1:B14,A1:A14)
=INTERCEPT(B1:B14,A1:A14)

these display:

-1.07032967
42.52747253

so the slope (and the amount dropped each month) are around -1. However to
get the %change per month, we draw the straight "fit" line:

=$G$2+A1*$G$1 and copy down in column C. We see:

1.00 38 41.46
2.00 39 40.39
3.00 41 39.32
4.00 34 38.25
5.00 43 37.18
6.00 39 36.11
7.00 38 35.04
8.00 32 33.96
9.00 38 32.89
10.00 25 31.82
11.00 33 30.75
12.00 30 29.68
13.00 24 28.61
14.00 29 27.54


The fit does not appear too bad. Now we can calculate the % change on the
"fit line. In D2 enter:

=(C2-C1)/C1 format as % and then copy down. We see:

1.00 38 41.46
2.00 39 40.39 -2.58%
3.00 41 39.32 -2.65%
4.00 34 38.25 -2.72%
5.00 43 37.18 -2.80%
6.00 39 36.11 -2.88%
7.00 38 35.04 -2.96%
8.00 32 33.96 -3.06%
9.00 38 32.89 -3.15%
10.00 25 31.82 -3.25%
11.00 33 30.75 -3.36%
12.00 30 29.68 -3.48%
13.00 24 28.61 -3.61%
14.00 29 27.54 -3.74%

and the average of column D is:

-3.10%


Now this makes sense intuitively. We are losing about one count per month.
If we started with 100 this would be 1% per month, but we are starting in the
30's, so the % drop is higher.

 

[Mike]

Gary's Student,

Thanks for the last reply that helps a lot, let me apply it to the other
data sets and see what I get...

Thanks a bunch