Microsoft Office Tutorials and References
In Depth Information
Calculating slope, y-intercept, and R-squared (Continued)
Assume that the y values are in B2:B11 and the x values are in A2:A11. To calculate
the slope, you can use the SLOPE function, as below:
Use the following formula to calculate the y-intercept:
Once you know the values for the slope and the y-intercept, you can calculate the
predicted y value for each x using a formula in the form of
y = mx +b
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 will yield more accurate predictions. Statistically
speaking, you can interpret R-squared as the proportion of the variance in y that is
attributable to the variance in x.
To calculate R-squared, you can use the RSQ function, as in this formula:
Or, calculate the correlation coefficient and square it:
Keep in mind that the value of R-squared calculated by the RSQ function or CORREL
function is valid only for a linear trendline.
Although linear trendlines are most common, an Excel chart can display nonlinear
trendlines of the following types:
Logarithmic: Used when the rate of change in the data increases or
decreases quickly and then flattens out.
Power: Used when the data consists of measurements that increase at a
specific rate. The data cannot contain zero or negative values.