Wednesday, November 24, 2004

Calculating Moving Averages in Excel

Reader's Request:

"How do I calculate a moving average in Excel?"

Moving averages are actually quite simple and can be done a number of ways.

Excel has a chart function allowing a moving average trendline to be added to a chart. Try it. Create a line or bar chart and then select a series, right click and select Add Trendline. A dialog box of alternative trendlines will pop up. Select Moving Average and click OK.
Figure 1.


Moving Averages of Data

The following image illustrates two moving average calculations for some sales data.
First I created a table using the RAND() function. I then calulated two moving averages. The first is a simple formula of =AVERAGE(year1:year2) with the year totals being relative and not absolute . This formula is copied down and each calculation is for the year that the formula is on and the previous year
The second formula uses the relative $ on the row number to lock the starting year. Thus the range that the average is calculated over is progressivly larger as it is copied down. The last calculation is the average of all four years. This result gives an average that becomes less and less susceptible to movement as time goes on.

Your choice of method is dependent on the result you want to achieve.
The second method is good for predicting seasonality, the first is better for calculating margin variances between years.
Figure 2.


Posted by: Jethro | 9:14 PM |