How to Use Method of Least Squares in Google Sheets

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 Google Sheets, we can use the LINEST() function.

The following step-by-step example shows how to use this function in practice.

Step 1: Enter the Data

First, let’s create the following dataset in Google Sheets:

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.

To do so, type the following formula into cell D1:

=LINEST(B2:B16, A2:A16)

The following screenshot shows how to use this formula in practice:

The coefficients of the regression line are shown in cells D1 and D2.

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 plot the dataset along with the fitted regression line.

To do so, highlight the cell range A2:B16, then click the Insert tab along the top ribbon, then click Chart.

In the Chart editor panel, choose Scatter chart as the chart type:

Then click the Customize tab in the Chart editor, then click the dropdown arrow next to Series, then scroll down and check the box next to Trendline:

The fitted regression line will appear on the scatter plot:

Google Sheets method of least squares

The individual points in the plot represent the (x,y) pairs from the original dataset and the straight line through the data points represents the fitted regression line.

Additional Resources

The following tutorials explain how to perform other common tasks in Google Sheets:

How to Create a Correlation Matrix in Google Sheets
How to Perform Logistic Regression in Google Sheets
How to Perform Polynomial Regression in Google Sheets

Leave a Reply

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