Microsoft Office Tutorials and References
In Depth Information
When your chart contains a trendline, you can instruct Excel to extend the trendline to forecast additional val-
ues. You do this on the Trendline Options section of the Format Trendline task pane. (Read earlier in this sec-
tion to see how to open this task pane.) Just specify the number of periods to forecast. Figure 17-27 shows a
chart with a trendline that's extended to forecast two subsequent periods.
If you know the values of the slope and y -intercept (see the “Calculating the slope and y -intercept” section,
earlier in the chapter), you can calculate forecasts for other values of x. For example, to calculate the value of y
when x = 11 (November), use the following formula:
You can also forecast values by using the FORECAST function. The following formula, for example, forecasts
the value for November (that is, x = 11) using known x and known y values:
Figure 17-27: Using a trendline to forecast values for two additional periods of time.
The accuracy of forecasted values depends on how well the linear trendline fits your actual data. The value of
R-squared represents the degree of fit. R-squared values closer to 1 indicate a better fit — and more accurate