Simple regression in Excel without fooling yourself

You have a sheet of 12 stores in a retail chain: each row is last quarter's advertising spend and revenue, in millions of dong. =SLOPE gives 7.59 and =RSQ gives 0.87, and you are about to write on a slide: "one more million of advertising adds 7.6 million of revenue; the model explains 87 per cent." What should you check first? This post follows the simple linear regression workflow: plot, fit, assess, then predict within limits. All three datasets are invented, generated in Python with fixed seeds, so I know the true mechanism that a real sheet would hide. To read a scatter plot before trusting it, see five questions to ask before trusting a chart.

Which Excel function gives which number?

SLOPE and INTERCEPT give the line, RSQ the share of variation explained, and STEYX the typical miss of revenue around the line, in revenue units. =LINEST(y, x, TRUE, TRUE) returns a whole grid of statistics.

Dataset A is 12 stores sorted by spend. The first three columns are the data; the last two are what the line 130.9 + 7.59 × spend gives, so you can check your own sheet:

Spend  Revenue  Floor area  Fitted  Residual
    8      233          75   191.7      41.3
   10      217          80   206.8      10.2
   14      194          40   237.2     -43.2
   18      231          60   267.6     -36.6
   19      226          55   275.1     -49.1
   21      339         110   290.3      48.7
   25      308          95   320.7     -12.7
   25      379         150   320.7      58.3
   31      331         120   366.2     -35.2
   34      389         135   389.0       0.0
   43      489         170   457.3      31.7
   50      497         190   510.4     -13.4

With spend in A2:A13 and revenue in B2:B13:

=SLOPE(B2:B13,A2:A13)                    7.59   revenue difference that goes with each extra million of spend
=INTERCEPT(B2:B13,A2:A13)                130.9  the line's value at spend = 0
=RSQ(B2:B13,A2:A13)                      0.868  share of variation explained
=STEYX(B2:B13,A2:A13)                    39.7   typical miss, million dong
=INDEX(LINEST(B2:B13,A2:A13,1,1),2,1)    0.936  standard error of the slope

The LINEST grid has five rows: coefficients (slope first, intercept second), their standard errors, R² and the standard error of the estimate, F and degrees of freedom, and two sums of squares, per Microsoft's page for the function. Wrapping it in INDEX avoids an array formula.

What do R² and the standard error tell you, and what not?

An R² of 0.868 means the line explains about 87 per cent of the variation in revenue across the 12 stores (James et al., 2021, p. 70). The standard error of 39.7 is the typical distance of revenue from the line; James and colleagues add that it is also how far off a prediction would be on average even if you knew the true line (James et al., 2021, p. 69).

Two cautions. What counts as a good R² depends on the field: close to 1 in physics, but in biology, psychology and marketing a value well below 0.1 may be more realistic (James et al., 2021, p. 70). Dataset A is prettier than that by construction. And with one X, R² equals the squared correlation (James et al., 2021, p. 71): =CORREL(A2:A13,B2:B13)^2 returns 0.868.

To test the slope, divide it by its standard error: 7.59 / 0.936 = 8.11 on n − 2 = 10 degrees of freedom; =T.DIST.2T(8.11,10) gives a p-value of about 0.00001 (James et al., 2021, p. 67). The 95 per cent confidence interval is 7.59 ± T.INV.2T(0.05,10) × 0.936, or 5.50 to 9.68; the book uses a rough ±2 standard errors (James et al., 2021, p. 66). A small p-value says so clear a relationship would be unusual if the two were truly unrelated (James et al., 2021, pp. 67-68). It says nothing about the line's shape or about what causes what.

What does a residual plot find that R² misses?

Patterns. With simple regression, plot the residuals against X; ideally nothing shows (James et al., 2021, pp. 93-94). A U shape means the relationship is not a straight line (James et al., 2021, p. 94); an inverted U says the same (my inference). A funnel, with residuals widening as predictions grow, means the variance is not constant (James et al., 2021, p. 96). With data in time order, neighbouring residuals that resemble each other, called tracking, mean the errors are correlated (James et al., 2021, pp. 94-95). In Excel, add a residual column and scatter it against X:

D2: =B2-(INTERCEPT($B$2:$B$13,$A$2:$A$13)+SLOPE($B$2:$B$13,$A$2:$A$13)*A2)    fill down to D13

Dataset B is 12 weeks of an online shop, weekly spend 5 to 60 million, with revenue levelling off. I generated it from the curve 420 × (1 − e^(−spend/30)) plus noise, so I know the true relationship bends. The straight line gives R² of 0.947, a slope of 5.29 and a p-value around 10⁻⁷: every number looks good. The residuals say otherwise:

Spend:     5  10  15  20  25  30  35  40  45  50  55  60
Revenue:  64 122 163 196 233 257 290 321 322 335 357 366
Residual: -43 -11   3  10  21  18  25  29   4 -10 -14 -32

Negative at both ends and positive in the middle: an inverted U. Adding a spend² column and running LINEST on the two adjacent X columns gives R² of 0.996 and a standard error of 6.5 instead of 23.6. The book suggests transforming X, with log X, √X or X², when the residuals show non-linearity (James et al., 2021, p. 94).

Can one point drag the whole line?

Yes, if its X is unusual. The book separates an outlier, whose Y is unusual given X, from a point whose X is unusual; removing the latter changes the line far more than removing an outlier (James et al., 2021, pp. 97-98). With one predictor, the book's statistic for row i is h = 1/n + (xᵢ − x̄)² / Σ(xᵢ − x̄)², which many packages call the hat value; its average is always (p + 1)/n, here 2/12 = 0.167, and a point far above that is suspect (James et al., 2021, p. 99). In Excel:

E2: =1/COUNT($A$2:$A$13)+(A2-AVERAGE($A$2:$A$13))^2/DEVSQ($A$2:$A$13)    fill down to E13

In dataset A, store 12 (spend 50) has the highest hat value, 0.435, but sits close to the line (residual −13.4), so it does no harm. Dataset C differs: 11 stores spending 8 to 28 million, with random revenue of 178 to 274 million unrelated to spend, plus one flagship spending 90 and selling 430.

Spend:       8  10  12  14  15  17  19  21  24  26  28  90
Revenue:   231 268 264 216 222 215 246 228 251 178 274 430

The flagship's hat value is 0.924. Its residual is only +17.8, smaller than the −79.6 of another store: the line is pulled towards it, so the residual plot looks calm. The book says it is a concern when a couple of points dominate the line, because any problem with them may invalidate the fit (James et al., 2021, pp. 98-99). Run =RSQ and =SLOPE twice, with and without the point, and report both.

Is the slope the effect of advertising?

No. The book defines the slope as the average increase in Y that goes with a one-unit increase in X, and the error term as the catch-all for what the model misses, including other variables that move Y (James et al., 2021, p. 63). I generated dataset A as revenue = 100 + 1.0 × spend + 1.8 × floor area + noise (standard deviation 16), and let bigger stores spend more: spend and area correlate at 0.87. The simple slope of 7.59 is 7.6 times the true effect of 1.0, because spend collects the credit that belongs to floor area.

The book shows the same thing: newspaper advertising looks related to sales on its own, but its coefficient is near zero once TV and radio join the model, because newspaper spend rises with radio spend (James et al., 2021, p. 74). Insert the floor-area column right after spend so the two X columns sit side by side (revenue moves to column C), then run =LINEST(C2:C13, A2:B13, TRUE, TRUE): the spend coefficient is 2.78 (95 per cent interval 0.87 to 4.70, containing the true 1.0), the area coefficient 1.48 and R² 0.977. Excel lists coefficients in the reverse order of the columns.

That interval is still wide: 12 rows and two strongly correlated variables are hard to separate, and a measured variable cannot remove an unmeasured one (my inference). The book separates prediction, where accuracy is enough, from inference, where you must understand the relationship (James et al., 2021, pp. 19-20). To estimate revenue for similar stores given a budget, the simple slope may do. To learn what 10 million more advertising would do at store X, the chain's observational data cannot answer directly; I suggest changing spend at a few comparable stores and keeping a few as benchmarks.

How far outside the data can you predict?

Only within the range of X you have seen, and even inside it the prediction interval is wide. Dataset A has spend from 8 to 50. At 30, =FORECAST(30,B2:B13,A2:A13) gives 358.6, with a 95 per cent prediction interval of 265.9 to 451.4. At 100 the prediction is 889.9, with an interval of 708 to 1,072. That interval still assumes the line holds at twice the highest spend seen; when computing such intervals the book ignores the error from a linear model being only an approximation (James et al., 2021, p. 82). The formula I used:

=FORECAST(x0,B2:B13,A2:A13) ± T.INV.2T(0.05,10)*STEYX(B2:B13,A2:A13)*SQRT(1+1/COUNT(A2:A13)+(x0-AVERAGE(A2:A13))^2/DEVSQ(A2:A13))

That is the standard textbook formula; I did not find it in the parts of the book I read.

Dataset B: the straight line predicts 504 at a spend of 80 and 609 at 100; the curve I built gives 391 and 405, levelling at 420. An R² of 0.947 gave no warning. The intercept of 130.9 in dataset A is an extrapolation too: no store spends less than 8, so do not read it as "revenue with no advertising".

Going deeper: what did I read, and what not?

James and colleagues (2021, 2nd edition): I read section 3.1 in full, section 3.2.1 up to table 3.5, the prediction-interval passage on page 82, section 3.3.3 problems 1 to 5 (pages 92 to 99) and the prediction-versus-inference passage in section 2.1. I did not read the collinearity section, the R labs, other chapters or exercises. My copy is the authors' PDF as archived by the Internet Archive on 19 November 2021; I could not download the corrected June 2023 version, so I have not compared the two. Page numbers are printed ones. The book does not use the word "extrapolation"; that section is my reasoning.

For Excel I read Microsoft's LINEST page (https://support.microsoft.com/en-us/excel/functions/linest-function) for the grid layout and the note on SLOPE; I did not read the ToolPak documentation or run Microsoft Excel. The data come from Python (A: seed 851, chosen after trying many seeds so the simple slope came out near 7.5, so not a random sample; B: seed 7; C: seed 11). Every statistic in the tables and code blocks was computed in Python and by formulas run in LibreOffice Calc, and they agree.

The biggest limit: the data are invented, so you see the traps with the truth known. Real data offer no such check.