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.