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:
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:
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:
The value of R-squared calculated by the RSQ function is valid only for a linear trendline.