The method of least squares is a method we can use to find the regression line that best fits a given dataset.
The following video provides a brief explanation of this method:
To use the method of least squares to fit a regression line in Excel, we can use the =LINEST() function.
The following step-by-step example shows how to use this function in practice.
Step 1: Create the Dataset
First, let’s create the following dataset in Excel:
Step 2: Use Method of Least Squares to Fit Regression Line
We can use the =LINEST(known_ys, known_xs) function to use the method of least squares to fit a regression line to this dataset:
Once we press ENTER, the coefficients of the regression model will appear:
Step 3: Interpret the Results
Using the coefficients from the =LINEST() function, we can write the following fitted regression line:
y = 11.55211 + 1.07949(x)
We can use this equation to estimate the value of y based on the value of x.
For example, if x = 10 then we would estimate that y would be equal to 22.347:
y = 11.55211 + 1.07949(10) = 22.347
Step 4: Plot the Results
Lastly, we can use the following steps to plot the dataset along with the fitted regression line:
- Highlight cells A2:B16.
- Click the Insert tab along the top ribbon. Then click the first chart option titled Insert Scatter (X, Y) or Bubble Chart in the Charts group.
- Once the chart appears, click the plus “+” sign in the top right corner. In the dropdown menu, click the checkbox next to Trendline to add the fitted regression line to the chart.
Additional Resources
How to Perform Multiple Linear Regression in Excel
How to Perform Quadratic Regression in Excel
How to Perform Polynomial Regression in Excel
Curve Fitting in Excel (With Examples)