Microsoft Office Tutorials and References

In Depth Information

**Working with Trendlines**

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

If you know the values of the slope and
y-intercept (see the “Calculating the slope and
y-inter-

cept” section, earlier in the chapter), you can calculate forecasts for other values of
For
x.

ple, 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)

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 predictions. In other words, you can interpret R-squared as the

proportion of the variance in
y attributable to the variance in
x.

As described previously, you can instruct Excel to display the R-squared value in the chart. Or

you can calculate it directly in your worksheet using the RSQ function. The following formula

calculates R-squared for
x values in B2:B11 and
y values for C2:C11:

=RSQ(B2:B11,C2:C11)

The value of R-squared calculated by the RSQ function is valid only for a linear trendline.