Table of Contents
The Method of Least Squares is a mathematical technique used to find the best-fitting line or curve for a set of data points. In Google Sheets, this method can be applied by first entering the data points into a spreadsheet, then using the “TREND” function to generate a line of best fit. The function calculates the slope and intercept of the line by minimizing the sum of the squared differences between the data points and the line. This method can be useful for analyzing trends and making predictions based on the data.
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:
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:
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: