Excel - Rate of change

Asked By Mik on 01-Apr-09 11:18 AM
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,

--
Mike




GarysStuden replied on 01-Apr-09 11:28 AM
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.
--
Gary''s Student - gsnu200842
Mik replied on 01-Apr-09 11:57 AM
I am not for sure how to do that but would'nt the function Slope give me the
rate of change?
--
Mike
GarysStuden replied on 01-Apr-09 12:43 PM
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

=(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.

--
Gary''s Student - gsnu200842
Mik replied on 01-Apr-09 12:51 PM
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
--
Mike