LINEST in Excel: multiple regression with a worked example

The LINEST syntax

=LINEST(known_y's, [known_x's], [const], [stats])

• known_y's: the column you want to explain, here monthly sales in D2:D13
• known_x's: one or more adjacent driver columns, here foot traffic and local ads in B2:C13
• const: TRUE to estimate an intercept (almost always what you want)
• stats: TRUE to get the full statistics block, not just the coefficients

In Excel 365 the result spills into a block of cells. In older versions select the block first and confirm with Ctrl+Shift+Enter. Either way, the clean approach is to pick single values with INDEX(LINEST(…), row, column), which works in every version and in Google Sheets too.

Step 1: read the output block

With two drivers and stats switched on, LINEST returns 5 rows and 3 columns:

• row 1: the coefficients, last x column first, then the intercept
• row 2: the standard error of each coefficient
• row 3: R² and the standard error of the estimate
• row 4: the F statistic and the residual degrees of freedom
• row 5: the regression and residual sums of squares

Because the x columns are B (traffic) and C (ads), column 1 of the output is the ads coefficient and column 2 the traffic coefficient. =INDEX(LINEST($D$2:$D$13,$B$2:$C$13,TRUE,TRUE),1,1) gives 5.118, column 2 gives 3.153 and column 3 the intercept, 30.90. The example labels the columns in that order so you can see it.

Step 2: is each driver significant?

Divide each coefficient by its standard error to get the t statistic: 5.118 ÷ 0.839 = 6.10 for ads and 3.153 ÷ 0.327 = 9.64 for traffic. The two-tailed p-value is =T.DIST.2T(ABS(t), df), with the degrees of freedom from row 4, column 2: n − k − 1 = 12 − 2 − 1 = 9. Both p-values are far below 0.05 (0.0002 for ads, below 0.0001 for traffic), so both drivers clearly matter. A rule of thumb: |t| above about 2 means the coefficient is clearly different from zero.

Read each coefficient as the effect of one unit of that driver holding the other constant: 1,000 more visitors a month go with $3,153 more sales, and $1,000 more local advertising with $5,118 more sales, at the same traffic.

Worked example: sales of 12 stores on foot traffic and local ads

Store data are yellow inputs (monthly, in thousands). Every number below the store list is a formula built on LINEST. Note the column order of the output: local ads first, then foot traffic, then the intercept.

Worked example: sales of 12 stores on foot traffic and local ads

	A	B	C	D
1	Store	Foot traffic (000)	Local ads ($000)	Sales ($000)
2	Ashby	18.4	6.0	128.6
3	Bramley	22.1	4.5	114.6
4	Carrow	25.7	9.2	163.4
5	Denton	29.3	7.1	153.5
6	Elmore	31.0	11.8	198.7
7	Fenwick	34.6	5.5	164.9
8	Garston	20.2	10.4	139.3
9	Hadley	27.5	3.8	145.3
10	Irwell	38.9	8.6	194.9
11	Jarrow	24.0	12.5	172.5
12	Kelso	33.2	9.9	178.1
13	Linwood	41.5	6.7	200.5
14
15	LINEST output	Local ads	Foot traffic	Intercept
16	Coefficient	5.118	3.153	30.90
17	Standard error	0.839	0.327	11.92
18	t statistic	6.10	9.64	2.59
19	p-value	0.0002	0.0000	0.0291
20	R²	0.934
21	Standard error of sales ($000)	7.89
22	Degrees of freedom	9
23	F statistic	63.9
24	Adjusted R²	0.920
25
26	New site	Foot traffic (000)	Local ads ($000)	Forecast sales ($000)
27	Morley	30.0	8.0	166.4
28	Check with TREND			166.4

Fictional stores. Twelve observations are enough to learn LINEST but thin for a real business model. Correlation in data like this does not prove that advertising causes the sales.

Step 3: R², adjusted R² and the forecast

R² = 0.934: the two drivers explain 93% of the variation in sales between stores. Adding drivers always raises R², even useless ones, so compare models with the adjusted R², =1-(1-R²)*(n-1)/df, here 0.920. The standard error of the estimate, 7.89 ($7,890), is the typical miss of the model for one store.

To forecast a new site with 30,000 visitors and $8,000 of ads: =intercept + b_traffic × 30 + b_ads × 8 = 166.4, or $166,400 a month. =TREND(D2:D13,B2:C13,B27:C27) returns the same number in one step. For a single driver, SLOPE, INTERCEPT and RSQ are simpler, as in the calibration curve guide.

LINEST or the Analysis ToolPak?

The Data Analysis ToolPak's Regression tool prints a labelled report with p-values and residuals, which is convenient for a one-off. It is a static copy, though: change one sales figure and the report does not update. LINEST is live, so the model, the forecast and every check recalculate as the data changes. Use the ToolPak to explore, LINEST to build a model you will keep.

Before trusting any regression, plot the residuals (actual − predicted) against the predicted values. They should scatter randomly around zero. A curve suggests a missing variable or a log model, a funnel suggests the errors grow with size. Measure forecast accuracy on new data too, for example with the MAPE.

Common mistakes

• Reading the coefficients in the wrong order. LINEST returns the last x column first and the intercept last. Label the output or check with a one-driver SLOPE.
• Leaving stats off. Without the fourth argument TRUE you only get the coefficients, no standard errors, R² or degrees of freedom.
• Using non-adjacent x columns. known_x's must be one contiguous block. Copy the drivers next to each other first.
• Comparing models by R² alone. R² always rises with more drivers. Use the adjusted R² and the t statistics.
• Forecasting far outside the data. The model only knows stores with 18,400 to 41,500 visitors. A site with 80,000 is an extrapolation, not a forecast.
• Ignoring correlated drivers. If two drivers move together, their coefficients become unstable. Check CORREL between the x columns first.