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.
Sample Dataset
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.
![least squares linear regression example](https://i0.wp.com/pythoninoffice.com/wp-content/uploads/2022/03/image-32.png?resize=724%2C541&ssl=1)
The following equation will solve the best b (intercept) and w (slope) for us:
![](https://i0.wp.com/pythoninoffice.com/wp-content/uploads/2022/03/image-21.png?resize=331%2C226&ssl=1)
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
![least squares regression implementation in Excel](https://i0.wp.com/pythoninoffice.com/wp-content/uploads/2022/03/image-28.png?resize=348%2C384&ssl=1)
Now we just need to plug in the formulas to solve for w:
![calculate the w slope term](https://i0.wp.com/pythoninoffice.com/wp-content/uploads/2022/03/image-24.png?resize=370%2C147&ssl=1)
Then we can solve for b:
![calculate the b intercept term](https://i0.wp.com/pythoninoffice.com/wp-content/uploads/2022/03/image-25.png?resize=363%2C128&ssl=1)
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
![Using the LINEST formula in Excel](https://i0.wp.com/pythoninoffice.com/wp-content/uploads/2022/03/LINEST_1.png?resize=415%2C491&ssl=1)
![Using the LINEST formula in Excel](https://i0.wp.com/pythoninoffice.com/wp-content/uploads/2022/03/LINEST_2.png?resize=413%2C489&ssl=1)
We get both the intercept and slope terms, and we can verify that they match our previous manual calculation above.
![Using the LINEST formula in Excel](https://i0.wp.com/pythoninoffice.com/wp-content/uploads/2022/03/image-30.png?resize=412%2C487&ssl=1)
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”.
![Plot data and fitted regression line in Excel](https://i0.wp.com/pythoninoffice.com/wp-content/uploads/2022/03/image-31.png?resize=840%2C374&ssl=1)