Last time, I defined what a moving average is and why you might need one. I then showed you how to calculate a moving average value in your Pivot table and then display it in a chart. I close by asking the question whether there was a easier way to draw the moving average on your chart. If all you need is to display the moving average on your chart and do not care what the values are, you can simply plot the [SalesAmount] field as a function of the day on the chart and then follow the simple steps that follow here.
If I right click inside the chart’s plot area (where the data is), I see a popup menu. Near the bottom of this menu I next click the Add Trendline option which displays the following panel.
The Trendline options begin with six different ways you can ask Excel to fit a line through the data points including everything from a straight line through polynomial, exponential, logarithmic, and power functions. However, the one I am interested in today is the last one, Moving Average.
Notice that after selecting this option, you have to define the number of period to include in the moving average where period is define by the units of the chart’s x-axis. If we were to select 4, the resulting line would exactly duplicate the line I already displayed using my calculated 4-day average. However, as I increase the number of days in the moving average, the fluctuations even out. The following figure shows a 15-day moving average as a green line which now clearly shows the sales rising through the end of July and then dropping off in August.
So if I can create a Moving Average by simply using the Trendline options for a chart, why would I ever go through the process of calculating the moving average numbers manually? The simple reason is that the Trendline option does not display the moving average values. On the other hand, I can always display a table of my daily sales along with the moving average values as shown in this figure.
Well, I know this topic was a little short today, but I’m at SQL Saturday Orlando conducting a presentation on the new features in PowerPoint 2012. If you have never been to a SQL Saturday event, you owe it to yourself to go to http://www.sqlsaturday.com and find out when the next one is in your area. It is a great way to get a day of free training on SQL Server and related topics (like PowerPivot) and to network with your peers of similar interest.
C’ya next time.