Linear regression: SLOPE, INTERCEPT and LINEST
Fit a straight line to your data, read what its slope means, predict new values, and get the full regression table that the ToolPak gives you. Advanced · 8 min · SLOPE, INTERCEPT, RSQ, FORECAST, LINEST, Regression
Correlation says two things move together. Regression says by how much, by fitting the straight line that best matches the data: y = intercept + slope × x.
Here a shop compares each month's advertising spend with its sales, both in thousands of dollars:
- y is the dependent variable, the one you want to explain or predict: sales.
- x is the independent variable, the predictor: ad spend.
Every regression function takes y first, then x. In Excel's Data → Data Analysis → Regression tool, the Input Y Range is the dependent variable (sales here) and the Input X Range holds the predictors. The functions below give the same numbers, and they keep up when the data change.
Steps
In
F2, find the slope of the best-fit line:=SLOPE(C2:C13, B2:B13). Sales (y) come first, then ad spend (x).In
F3, find where the line crosses the y axis, the sales you would expect with no advertising:=INTERCEPT(C2:C13, B2:B13)How well does the line fit? In
F4, use=RSQ(C2:C13, B2:B13).Use the line to predict. The plan is to spend $25k (
F6). InF7, apply intercept + slope × x with the cells you just filled:=F3 + F2*F6FORECAST does the same in one step. In
F8, use=FORECAST(F6, C2:C13, B2:B13): the new x, then the known y and x values.Finally, the whole regression table in one formula. In
H2, type=LINEST(C2:C13, B2:B13, TRUE, TRUE). The last TRUE asks for the statistics, and the result spills intoH2:I6.
Remember
- The best-fit line is y = intercept + slope × x. y is the dependent variable (what you predict) and x is the independent one (the predictor).
=SLOPE(y, x)and=INTERCEPT(y, x): y always comes first.=RSQ(y, x)is how much of y the line explains.=FORECAST(new_x, y, x)predicts. Be wary of x values outside the range you have data for.=LINEST(y, x, TRUE, TRUE)spills the full regression table, like the ToolPak's Regression tool, but it updates live.
In the function reference: FORECAST, INTERCEPT, LINEST, RSQ, SLOPE
Previous: Correlation: do two things move together? · Next: Forecasting a trend with TREND