StatPakHow to run a regression in Google Sheets
A regression estimates how much an outcome changes when each input changes. This guide uses a small marketing dataset: monthly sales, ad spend and price for 15 months.
| A | B | C | |
|---|---|---|---|
| 1 | Ad spend | Price | Sales |
| 2 | 12 | 8.2 | 37.8 |
| 3 | 15 | 9.6 | 45.2 |
| 4 | 9 | 7.4 | 39.8 |
| 5 | 22 | 9.1 | 66 |
| 6 | 18 | 10.3 | 52.5 |
| 7 | 25 | 7.9 | 88.3 |
| 8 | 30 | 9.4 | 90.5 |
| 9 | 14 | 8.8 | 47.4 |
| 10 | 20 | 7.1 | 77.2 |
| 11 | 27 | 10 | 79.7 |
| 12 | 33 | 8.5 | 108.6 |
| 13 | 11 | 9.9 | 27.5 |
| 14 | 24 | 7.6 | 81.5 |
| 15 | 29 | 8.9 | 95.5 |
| 16 | 17 | 10.4 | 45 |
Run it with the Regression tool
- Open Extensions, StatPak, Data Analysis and choose Regression.
- Input Y range: the Sales column including its header,
C1:C16. - Input X range: Ad spend and Price together,
A1:B16. X columns must sit next to each other. - Tick Labels in first row so the output uses your column names.
- Click Run.
You get the same summary that Excel produces:
| SUMMARY OUTPUT | ||||||||
| Regression Statistics | ||||||||
| Multiple R | 0.9947242 | |||||||
| R Square | 0.9894762 | |||||||
| Adjusted R Square | 0.9877223 | |||||||
| Standard Error | 2.761873 | |||||||
| Observations | 15 | |||||||
| ANOVA | ||||||||
| df | SS | MS | F | Significance F | ||||
| Regression | 2 | 8606.425 | 4303.212 | 564.138 | 1.3584E-12 | |||
| Residual | 12 | 91.53531 | 7.627942 | |||||
| Total | 14 | 8697.96 | ||||||
| Coefficients | Standard Error | t Stat | P-value | Lower 95% | Upper 95% | Lower 95.0% | Upper 95.0% | |
| Intercept | 54.04907 | 6.470921 | 8.352609 | 2.4111E-6 | 39.95014 | 68.148 | 39.95014 | 68.148 |
| Ad spend | 3.20781 | 0.09879192 | 32.47037 | 4.6043E-13 | 2.992561 | 3.423059 | 2.992561 | 3.423059 |
| Price | -6.084341 | 0.6898521 | -8.819777 | 1.3663E-6 | -7.5874 | -4.581283 | -7.5874 | -4.581283 |
How to read the output
- R Square is 0.9894762: the two inputs explain 98.9% of the month-to-month variation in sales. With several inputs, report Adjusted R Square (0.9877223), which penalises adding inputs that do not help.
- Significance F (1.3584E-12) tests whether the model as a whole explains anything. A value below 0.05 means it does.
- Coefficients: each extra unit of ad spend goes with 3.20781 more units of sales, holding price fixed. Each unit of price goes with -6.084341 units of sales.
- P-value for each input tests whether its coefficient is different from zero. Both are far below 0.05 here.
- Lower 95% and Upper 95% give the range the true coefficient likely falls in. For ad spend it is 2.992561 to 3.423059.
The same numbers with LINEST
For a quick check without an add-on, enter this in an empty cell:
=LINEST(C2:C16, A2:B16, TRUE, TRUE)
LINEST returns coefficients in reverse order (last X column first, intercept last) and puts standard errors in the second row and R squared in the third. It does not give p-values or confidence intervals; compute those with =T.DIST.2T(ABS(coef/se), df) and =T.INV.2T(0.05, df).
Simple regression with one input
With only ad spend as the input, R Square is 0.9212573 and the slope is 3.201894. In Sheets you can also get these with =SLOPE(C2:C16, A2:A16), =INTERCEPT(C2:C16, A2:A16) and =RSQ(C2:C16, A2:A16).
Common errors
- "Input X Range has N rows": Y and X must have the same number of rows.
- "X columns are linearly dependent": one input is an exact combination of others (for example a total column next to its parts). Remove it.
- Blank or text cells: regression needs a complete numeric block. Delete or fill empty rows first.