EXCEL2010 Chart Series Add trend lines

In non-stacked graphs, two-dimensional, area, bar, column, line, stock, xy (scatter), and bubble charts, click the data series to which you want to add a trendline or move average, or do the following to select a data series from a list of chart elements:

Click anywhere within the chart.

This displays the chart tool with the design, layout, and Format tabs.

On the Format tab, in the current selection group, click the arrow next to the Chart elements box, and then click the chart element that you want.

Note If you select a chart with more than one data series, and you do not select a data series, Excel displays the Add Trendline dialog box. In the list box, click the data series that you want, and then click OK.

On the Layout tab, in the Analysis group, click Trendline.

Do one of the following:

Click the predefined trendline option you want to use.

Note This applies a trendline without allowing you to select specific options.

Click More trendline options, and then in the trendline options category, under trend prediction/regression type, click the type of trendline you want to use.

Use this type to create

Linear trendline that calculates the least square fit of a line by using the following formula:

where m is the slope and B is the Intercept.

logarithms compute the logarithmic trendline for the least square fit of a point by using the following formula:

where C and B are constants and LN is a natural logarithmic function.

Polynomials use the following formula to compute a polynomial or curved trendline for the least square fit of a point:

of which, B and

As constants.

Power by using the following formula to compute the power trendline for the least square fit of a point:

where C and B are constants.

Note This option is not available when the data includes a negative value or 0 values.

An exponential trendline that calculates the least square fit of a point by using the following formula:

where C and B are constants, and E is the base of the natural logarithm.

Note This option is not available when the data includes a negative value or 0 values.

Move average trendline using the following formula:

Note The data points for the moving average trendline are equal to the total number of data points in the data series minus the number specified for the cycle.

The R-squared value displays the R-squared trendline on the chart by using the following formula:

This trendline option is available on the Options tab of the Add Trendline or Format Trendline dialog box.

Note the R-squared value that you can display with a trendline is not the adjusted R-squared value. For logarithmic, power, and exponential trendlines, Excel uses the converted regression model.

If you select polynomial, in the number box, type the maximum power for the argument.

If you select Move average, in the period box, type the number of periods that you want to use to calculate the moving average.

Comments

If you add a moving average to an xy (scatter) chart, the move average is based on the order of the X values plotted in the chart. To get the results you want, you might have to sort the x values before adding a move average.

If you add a trendline to a line chart, column chart, area chart, or bar chart, the trendline is calculated based on the assumption that x values are 1, 2, 3, 4, 5, 6, and so on. This assumption is made regardless of whether the X value is numeric or literal. To base a trendline on a numeric x value, you should use an xy (scatter) chart.

Excel automatically assigns a name to the trendline, but you cannot change the name. In the Format Trendline dialog box, under trendline name in the trendline options category, click Custom, and then type a name in the Customize box.