Course / Data Analysis

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:

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

  1. In F2, find the slope of the best-fit line: =SLOPE(C2:C13, B2:B13). Sales (y) come first, then ad spend (x).

  2. In F3, find where the line crosses the y axis, the sales you would expect with no advertising: =INTERCEPT(C2:C13, B2:B13)

  3. How well does the line fit? In F4, use =RSQ(C2:C13, B2:B13).

  4. Use the line to predict. The plan is to spend $25k (F6). In F7, apply intercept + slope × x with the cells you just filled: =F3 + F2*F6

  5. FORECAST 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.

  6. 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 into H2:I6.

Remember

In the function reference: FORECAST, INTERCEPT, LINEST, RSQ, SLOPE

Previous: Correlation: do two things move together? · Next: Forecasting a trend with TREND