Adding Moving Averages and Other Trendlines


A trendline is a line that describes the general tendency of a data series. It can be a moving average, a linear-regression line, or a line generated by one of various kinds of nonlinear curve-fitting methods.

To add a trendline to a series in an area, bar, column, line, or X Y (scatter) chart, right-click the series, and click Add Trendline. The dialog box shown in Figure 20-23 appears. (Alternatively, you can select any part of the chart, click the Layout tab, and then click Trendline in the Analysis group. The options that descend from the Ribbon when you do this don't cover the full gamut of trendline capabilities, however, and you'll probably want to click More Trendline Options at the bottom of the list-which will take you to the dialog box shown in Figure 20-23. It's simpler to right-click and use the shortcut menu.)

image from book
Figure 20-23: Using the Format Trendline dialog box, you can add six different types of trendlines to a data series.

To specify the type of trendline or moving average you want, select one of the six options at the top of the dialog box. If you choose Polynomial, indicate the highest power (from 2 through 6) for the independent variable in the adjacent Order text box. If you select Moving Average, indicate the number of periods Excel should use in its calculations.

After you've chosen the type of trendline you want, you can use the Trendline Name area of the dialog box to customize the name that will appear in the chart's legend. Options under Forecast (not available for moving averages) let you extrapolate the trendline. If you're adding a linear, polynomial, or exponential trendline, you can also set the y-intercept in the Set Intercept box and display the regression equation and R-squared value on the chart.

Note 

You can find a more detailed treatment of the Excel trendline features and their use in modeling growth in Microsoft Office Excel 2007: Data Analysis and Business Modeling, by Wayne L. Winston (Microsoft Press, April 2007).



Microsoft Office Excel 2007 Inside Out
MicrosoftВ® Office ExcelВ® 2007 Inside Out (Inside Out (Microsoft))
ISBN: 073562321X
EAN: 2147483647
Year: 2004
Pages: 260

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net