Microsoft Office Tutorials and References
In Depth Information
For each value of x (the horizontal axis), you can calculate the predicted value of y (the value on the trendline)
by using this equation. The variable m represents the slope of the line, and b represents the y -intercept. For ex-
ample, when x is 3 (for March), the predicted value of y is 674.47, calculated with this formula:
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 trendline is
most reliable when its R-squared value is closer to 1.
Calculating the slope and y-intercept
This section describes how to use the LINEST function to calculate the slope ( m ) and y -intercept ( b ) of the best-
fit linear trendline.
Figure 17-25 shows ten data points ( x values in column B, actual y values in column C).
Figure 17-25: Using the LINEST function to calculate slope and y -intercept.