Multiple regression in Google Sheets

Multiple regression predicts one outcome from several inputs at once and tells you the effect of each input while the others are held fixed. Example: sale prices of 16 homes (in $000) with their size, number of bedrooms, age and whether they have a garage.

	A	B	C	D	E
1	Sq ft	Bedrooms	Age	Garage	Price
2	1450	3	22	0	188
3	1720	3	15	1	252
4	2100	4	8	1	314
5	1280	2	35	0	153
6	1950	3	12	1	285
7	2400	4	5	1	355
8	1600	3	28	0	203
9	1850	3	18	1	268
10	2250	4	10	1	333
11	1380	2	40	0	163
12	2050	4	14	0	282
13	1700	3	25	1	233
14	2600	5	3	1	375
15	1500	3	30	0	198
16	1980	4	9	1	288
17	2300	4	16	1	324

Garage is a dummy variable: 1 if the home has a garage, 0 if it does not. More on that below.

Run it with the Regression tool

Open Extensions, StatPak, Data Analysis and choose Regression.
Input Y range: the Price column with its header, E1:E17.
Input X range: all four inputs with headers, A1:D17. The X columns must be next to each other, so keep the outcome column to the right or left of them.
Tick Labels in first row and click Run.

SUMMARY OUTPUT

Regression Statistics
Multiple R	0.9982905
R Square	0.9965838
Adjusted R Square	0.9953416
Standard Error	4.672309
Observations	16

ANOVA
	df	SS	MS	F	Significance F
Regression	4	70053.61	17513.4	802.2459	1.7625E-13
Residual	11	240.1352	21.83047
Total	15	70293.75

	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%	Lower 95.0%	Upper 95.0%
Intercept	31.90495	18.33649	1.739971	0.1097281	-8.453386	72.26328	-8.453386	72.26328
Sq ft	0.1376293	0.009999752	13.76327	2.8094E-8	0.1156199	0.1596386	0.1156199	0.1596386
Bedrooms	-3.706355	4.834903	-0.7665832	0.4594652	-14.3479	6.935194	-14.3479	6.935194
Age	-1.277329	0.3050412	-4.187397	0.001517494	-1.94872	-0.6059373	-1.94872	-0.6059373
Garage	13.00725	4.30189	3.023613	0.0115807	3.538857	22.47565	3.538857	22.47565

How to read the output

Model fit

R Square is 0.9965838: the four inputs together explain 99.7% of the variation in price.
Adjusted R Square (0.9953416) corrects R Square for the number of inputs. R Square never falls when you add an input, even a useless one. Adjusted R Square falls if the new input adds less than chance would. Use it to compare models with different numbers of inputs.
Standard Error (4.672309) is the typical size of a prediction error, in the units of Y. Here that is about $5,000.
Significance F (1.7625E-13) is the p-value for the model as a whole. Below 0.05 means at least one input is related to price.

Coefficients

Each coefficient is the change in Y for a one-unit change in that input, with all the other inputs unchanged.

Sq ft: 0.1376293 thousand dollars per square foot, or about $13.8k for each extra 100 square feet.
Age: -1.277329, so each extra year of age lowers the price by about $1.3k.
Garage: 13.00725, so homes with a garage sell for about $13.0k more than comparable homes without one.
Bedrooms: -3.706355. The sign looks odd, but size is already in the model, so an extra bedroom at the same square footage means smaller rooms.

P-values

The P-value for each coefficient tests whether it differs from zero. Sq ft (2.8094E-8), Age (0.001517494) and Garage (0.0115807) are below 0.05. Bedrooms (0.4594652) is not: once size, age and garage are known, the data cannot tell its effect apart from zero. Its 95% interval, -14.3479 to 6.935194, includes zero, which says the same thing.

If you drop Bedrooms (move it out of the X range so the remaining X columns stay adjacent) and run again, Adjusted R Square rises from 0.9953416 to 0.9955017. The simpler model fits slightly better once you account for the number of inputs.

Dummy variables

Regression needs numbers, so a category becomes 0/1 columns. A yes/no category needs one column, like Garage above. A category with k levels needs k - 1 columns: for three neighborhoods (North, Central, South) add a Central column and a South column, each 1 or 0. North, the level with no column, is the baseline, and each dummy's coefficient is the difference from North.

Do not add a column for every level. The columns would always add up to 1, which duplicates the intercept, and StatPak stops with an error that the X columns are linearly dependent. You can build dummies with a formula such as =IF(C2="South", 1, 0).

The same coefficients with LINEST

=LINEST(E2:E17, A2:D17, TRUE, TRUE) returns a 5 by 5 block. The first row holds the coefficients in reverse order: Garage, Age, Bedrooms, Sq ft, then the intercept. The second row holds their standard errors and the third row starts with R squared. LINEST gives no p-values or adjusted R square. Compute a p-value with =T.DIST.2T(ABS(coefficient / standard error), 11), where 11 is the residual df (16 observations minus 5 coefficients), and adjusted R square with =1 - (1 - R2) * (16 - 1) / (16 - 4 - 1).

Checks before you trust the result

Enough rows: aim for at least 10 to 20 observations per input. With 16 homes and four inputs this example is at the low end.
Inputs that move together: if two inputs are strongly correlated, their coefficients become unstable and their p-values grow. Check with a correlation matrix first.
Residuals: tick Residuals in the Regression tool and look for patterns. Residuals that grow with the prediction or curve suggest a missing input or a transformation such as a log.