Microsoft Office Tutorials and References
In Depth Information
Working with Trendlines
The R-squared value is 0.67.
What do these numbers mean? You can describe a straight line with an equation of the form:
y = mx +b
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 example, 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 at or near 1.
Calculating the slope and y-intercept
As you know, Excel can display the equation for the trendline in a chart. This equation shows the
slope (m) and y-intercept (b) of the best-fit trendline. You can calculate the value of the slope
and y-intercept yourself, using the LINEST function in a formula.
Figure 17-24 shows 10 data points ( x values in column B, actual y values in column C).
Figure 17-24: Using the LINEST function to calculate slope and y-intercept.
The formula that follows is a multicell array formula that displays its result (the slope and y-inter-
cept) in two cells: