Simple Linear Regression
Simple Linear Regression In Excel Versions 5 and above, a regression line (or trendline) can be added to a scatterplot by right-clicking on one of the plotted points and selecting Add Trendline from the shortcut menu. Both linear and a variety of non-linear models may be fitted to the data. The equation of the fitted model may be displayed, together with the value of the coefficient of determination, R2. There are also options to extrapolate the trendline in either direction, or to force the trendline to have a specific intercept.
The trendline approach is purely graphical. To calculate predictions, regression functions must be used.
Assuming a sample of values of the independent variable in range x, and corresponding values of the dependent variable in range y:
| Least squares estimate of intercept, a | =INTERCEPT(y, x) |
| Least squares estimate of slope, b | =SLOPE(y, x) |
| Sxy | =SUMPRODUCT(x, y)-COUNT(x)*AVERAGE(x)*AVERAGE(y) |
| Sxx | =DEVSQ(x) |
| Syy | =DEVSQ(y) |
| Sample covariance, Cov(x,y) | =COVAR(x, y)*COUNT(x)/(COUNT(x)-1) |
| Estimate of $$\sigma$$, s | =STEYX(y, x) |
| Prediction of y at x=x0, $$\hat{y}=a + bx_0$$ | =FORECAST(x0, y, x) |
| Estimated standard error of individual predicted y at x=x0 | ||
| =STEYX(y, x)*SQRT(1+1/COUNT(x)+( x0-AVERAGE(x))^2/DEVSQ(x)) | ||
| Estimated standard error of mean predicted y at x=x0 | ||
| =STEYX(y, x)*SQRT(1/COUNT(x)+( x0-AVERAGE(x))^2/DEVSQ(x)) | ||