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