Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rate of change
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rate of change
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 "Mike" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rate of change
I am not for sure how to do that but would'nt the function Slope give me the
rate of change? -- Mike "Gary''s Student" wrote: 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 "Mike" wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rate of change
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. -- Gary''s Student - gsnu200842 "Mike" wrote: I am not for sure how to do that but would'nt the function Slope give me the rate of change? -- Mike "Gary''s Student" wrote: 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 "Mike" wrote: 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rate of change
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 "Gary''s Student" wrote: 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. -- Gary''s Student - gsnu200842 "Mike" wrote: I am not for sure how to do that but would'nt the function Slope give me the rate of change? -- Mike "Gary''s Student" wrote: 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 "Mike" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I calculate instantaneous rate of change in excel? | Excel Discussion (Misc queries) | |||
Rate Change in an Amortization Schedule | Excel Discussion (Misc queries) | |||
Data Series rate of change (trend) | Excel Worksheet Functions | |||
Calculate Rate of Change Formula | New Users to Excel | |||
How can I change the commission rate% for different employees? | Excel Discussion (Misc queries) |