## Recipe 6.2. Adding Trendlines## ProblemYou've plotted your time series using Excel's charting feature and now you'd like to add a trendline. ## SolutionFigure 6-2 shows an example linear trendline you can create on Excel charts. Notice that you also have the option of displaying the trendline equation on the chart, as shown in the figure. ## Figure 6-2. Linear trendlineExcel also supports nonlinear trendlines . Figure 6-3 shows the same time series with a second-order polynomial trendline instead of a linear trendline. ## Figure 6-3. Polynomial trendlineTo create a trendline for a given data series, select the series by clicking on it and then right-click to reveal a pop-up menu. Select Add Trendline from the pop-up menu to open the Add Trendline dialog box, where you can select the type of trendline and other options. Refer to Chapter 8 to learn more about adding these and other types of trendlines to Excel charts. |

## Recipe 6.3. Computing Moving Averages## ProblemYou'd like to smooth a set of time series data by computing moving averages. ## SolutionYou can compute moving averages in several ways in Excel. You can use the built-in Moving Average trendline charting feature, the Analysis ToolPak Moving Average feature, or spreadsheet functions to compute moving averages. ## DiscussionExcel allows you to compute moving averages in several ways. I'll show you three ways: using chart trendlines, using the Analysis ToolPak, and using spreadsheet formulas. ## Moving Average trendlineAs mentioned in Recipe 6.2, you can add a trendline to a chart by selecting the data series and then right-clicking to reveal a pop-up menu. You can then select Add Trendline to open the Add Trendline dialog box shown in Figure 6-4. ## Figure 6-4. Add Trendline dialog boxYou can select from six different types of trendlines. In this case, select the Moving Average type. To the right of the Moving Average icon, you should see a Period spinner control (as shown in the figure). Use the spinner arrows to select the period over which averages will be computed. You can also type in a number. In this example, I entered 3 so as to create a three-year moving average trendline. On the Options tab you can type in a name for the new trendline. Figure 6-5 shows the Options tab. ## Figure 6-5. Add Trendline: Options tabMoving Average trendlines have available only the "Trendline name" options. The other options apply to the other trendline types shown in Figure 6-4. (See Chapter 8 for examples using the other trendline types.) Pressing OK closes the Add Trendline dialog box and adds the new trendline to your chart. Figure 6-6 shows the time series from Figure 6-2 and Figure 6-3 with a three-year moving average trendline. ## Figure 6-6. Three-year moving average trendlineIf you decide to modify the resulting moving average trendline, perhaps changing the averaging period, simply select the trendline on the chart and then right-click to reveal a pop-up menu. Next, select Format Trendline from the pop-up menu to open the Format Trendline dialog box.
The Format Trendline dialog box includes the same the Add Trendline dialog box, where you can change the trendline type and any of its options. Further, the Format Trendline dialog box has an additional tab called Patterns that allows you to format the actual line drawn for the trendline. For example, you can change the line weight, color, and style. Chart trendlines offer an easy way of creating moving average trendlines for time series data. However, this method does not allow you to extract the actual numerical values representing the moving average trendline. Also, this method requires that you first create a chart of your data. If you need numerical values for the trendline or you don't want to create a chart, then you can use one of the two following approaches. ## Analysis ToolPak Moving AverageThe Analysis ToolPak includes a Moving Average feature that allows you to compute moving averages in a manner similar to that of the Excel chart Moving Average trendline. The benefit of using the Analysis ToolPak is that you get the actual numerical data for the moving average series and not just a chart of the data. To compute moving averages for a data series, select Tools Data Analysis... from the main menu bar to open the Data Analysis dialog box. Figure 6-7 shows the Data Analysis dialog box, where youre presented with a list of analysis tools to choose from. ## Figure 6-7. Data Analysis dialog boxSelect Moving Average from this list and press OK to open the Moving Average dialog box shown in Figure 6-8. ## Figure 6-8. Moving Average dialog boxIn the Input Range field, enter (or select directly on your spreadsheet) the cell range containing the y values of the data series for which you'd like to compute moving averages. For my example, cells O3 to O72 contained the relevant data. Next, enter the Interval over which you'd like to compute averages. This is the same as the Period field used in the Trendline dialog box, as discussed earlier. Finally, enter the topmost cell in the range where you'd like the output created. In this case I selected cell P3, to place the moving average data adjacent to the original time series data. You may also elect to have a chart created automatically or have standard errors computed. Press OK to close the Moving Average dialog box and create the data. Your spreadsheet will now contain the new moving average data. Figure 6-9 shows a portion of the average annual temperature data series shown in Figure 6-6, along with the computed moving averages. ## Figure 6-9. Moving Average resultsNotice that the first two entries in the moving average series are In Figure 6-9, I intentionally selected one of the computed moving averages to show you that these results are not static numerical values. Instead, the Moving Average tool entered formulas to compute the moving average. The formula used is the standard At this point, you can perform other spreadsheet operations on the moving average data or plot the data as you wish. This approach gives you greater flexibility, as opposed to the trendline moving average method discussed earlier. However, what if you want to compute the moving averages over a period centered about each data point in the original series? You can use spreadsheet formulas to accomplish this, as discussed in the next section. ## Spreadsheet formulasAside from the two previous approaches, you can always use simple spreadsheet formulas to compute moving averages. Specifically, you can use the ## Figure 6-10. Centered moving averagesFigure 6-10 shows the same original data and three-year moving average data computed using the Analysis Toolpak from the previous section. Further, Figure 6-10 shows a new column of three-year moving averages computed using the The result of these simple steps yields a new data series containing three-year centered moving averages. This approach is direct in that you don't need to use charts first if you don't want to, nor do you need to use the Analysis ToolPak. Further, this approach is flexible. For example, you can change the cell range passed into the ## See AlsoYou can also compute weighted moving averages in Excel. See Recipe 6.4 to learn how. |