Microsoft Office Tutorials and References

In Depth Information

{=TREND(C2:C11,B2:B11)}

Linear forecasting

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:

=(53.194*11)+514.93

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:

=FORECAST(11,C2:C11,B2:B11)

Figure 17-27:
Using a trendline to forecast values for two additional periods of time.

Calculating R-squared

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