Step-by-step guide to do a linear regression on Google Sheets
In this example, we will use (fake) sales data to make predictions. Let's get to it!
1. Download the (fake) sales data:
Click on "Download raw file" (see image below)
2. Import data.csv on Google Sheets:
Google might ask you to sign in first (if you don't have an account, you need to create one first)
Start a new (blank) spreadsheet
Go to File -> Import (see image below)
3. Select Upload:
Go to the "Upload" page and click on "Browse" (see image below)
4. Select recently downloaded data.csv file and import data:
Click on "Import data" after selecting the data.csv file (see image below)
5. Check spreadsheet:
The data should have been imported, you should see something like the image below:
6. Make predictions:
Go to cell C2 and insert the following:
=FORECAST(A2,INDIRECT("B2:B51"), INDIRECT("A2:A51"))
This will use half of the data (the first 50 data points) to predict C2 sales.
The value on C2 should be very close to the value on B2.
7. Make full predictions:
Now press blue circle and spread formula to all remaining cells (see image below).
You should then see something like this:
So now you have a sales prediction for any day!
8: Select all three columns:
Select columns A to C (see image below)
9: Let's plot it:
Click on Insert -> Chart (see image below).
You should then see something like the image below:
10: Convert to scatter plot:
In the Chart editor (if not open, double click on the chart), change the Chart type to Scatter chart (see image below).
Let's also change the point size to 2px, for better visualization:
11: Let's get the slope and the intercept
Besides getting a forecast, we can also get the slope and intercept of the line resulting from the linear regression. For that, you can use the following formulas on new cells:
=SLOPE(B2:B51,A2:A51)
=INTERCEPT(B2:B51,A2:A51)
That is all!
You can find my spreadsheet here:
For more details on the FORECAST function, follow this link:
Comments