Microsoft Office Tutorials and References
In Depth Information
In other words, 164.16 is the y value on the linear trend line when x is 67. If a
new 6'0" member were recruited for the team, the best guess of his weight would
185.57, as calculated by this formula:
y = 4.2827 * (72) - 122.78
The R-squared value, sometimes referred to as the coefficient of determination ,
ranges in value from 0 to 1. This value indicates how closely the estimated values
for the trendline correspond to the actual data — a “goodness of fit” measure of the
overall reliability of the trend. A trendline is most reliable when its R-squared value
is near 1, and is least reliable when it’s near 0. If all the data points fell exactly on
the trendline, the R-squared value would be 1.0.
A much simpler way to generate the predicted y values for a linear trend line
is to use the TREND function in a multicell array formula. Using the
preceding example, select D4:D18 and enter the following array formula:
Enter the formula with Ctrl+Shift+Enter. The range will display the predicted
y values for the data in B4:B18.
Thus far, the discussion has focused on making predictions for data that falls within
the existing numerical range ( interpolation ). In addition, it’s possible to make
estimates for data that falls outside the existing range of data. This is known as
forecasting or extrapolation .
When your chart contains a trendline, you can instruct Excel to extend the
trendline to forecast additional values of x. You do this on the Options tab in the
Format Trendline dialog box (or the Options tab in the Add Trendline dialog box).
Just specify the number of periods to forecast (either forward or backward in time).
Figure 5-9 shows a line chart with monthly sales data for 21 months, along with
a trendline that forecasts results for three subsequent months. The forecasted data is
derived by simply extending the linear trendline to cover three additional periods.
Keep in mind that, because the category axis displays non-numeric data,
Excel uses consecutive integers in place of the month names.