Last Updated on March 7, 2022 by Jay
This tutorial will show you how to do a least squares linear regression with Excel using the example we discussed earlier. Check here to learn what a least squares regression is.
We’ll use the following 10 data pairs. Assuming the data is in a 2D space with x and y axis. The data point pairs represent the x and y coordinates in the 2D space.
x = [12,16,71,99,45,27,80,58,4,50] y = [56,22,37,78,83,55,70,94,12,40]
Least Sqaure Formula
As a reminder, the problem we want to solve is to find a line that best represents/fits the given data points. In order to find that best line y = b + wx, we need to find the b and w values that minimize the sum of squared errors.
The following equation will solve the best b (intercept) and w (slope) for us:
Least Squares Linear Regression Implementation In Excel
Let’s enter the following values into Excel:
- Column B – x values
- Column C – y values
- Then, column D = x^2
- Finally, column E = x * y
- N = # of data points, 10 in our example
- Don’t forget to sum up all the above values in row 12
Now we just need to plug in the formulas to solve for w:
Then we can solve for b:
This means that the line we are looking for is: y = 31.82863 + 0.495051x
The Excel LINEST() Formula
Luckily, there’s an Excel formula that can do all the heavy-lifting for us! That is the LINEST() function.
NOTE – LINEST is an array formula that returns 2 values: the intercept b and the slope w. The correct way to enter this formula is:
- Select 2 cells, B21 and C21 in our case
- Enter LINEST(y_range, x_range)
- Press Ctrl + Shift + Enter together
We get both the intercept and slope terms, and we can verify that they match our previous manual calculation above.
Plot Data And Regression Line In Excel
We can plot the data points as a scatter plot and even show the fitted regression line using the following steps:
- Select range B2:C11
- Insert -> Charts -> Scatter
- Then select the Chart, on the top right corner there should be a green plus + sign. Click on it and check “Trendline”.