Least Squares Linear Regression With Excel

Sharing is caring!

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
A least squares linear regression example

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
least squares regression implementation in Excel
least squares regression implementation in Excel

Now we just need to plug in the formulas to solve for w:

calculate the w slope term
calculate the w slope term

Then we can solve for b:

calculate the b intercept term
calculate the b intercept term

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:

  1. Select 2 cells, B21 and C21 in our case
  2. Enter LINEST(y_range, x_range)
  3. Press Ctrl + Shift + Enter together
Using the LINEST formula in Excel
Using the LINEST formula in Excel
Using the LINEST formula in Excel
Using the LINEST formula in Excel

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
Using the LINEST formula in Excel

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:

  1. Select range B2:C11
  2. Insert -> Charts -> Scatter
  3. 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
Plot data and fitted regression line in Excel

Additional Resources

Least Squares Linear Regression with An Example

Leave a Reply

Your email address will not be published. Required fields are marked *