30. Statistics for Analysts

30.5 Simple Regression with SLOPE, INTERCEPT and LINEST: Predict Orders from Ad Spend

By Ravindra Bagale · Last updated: 7 October 2026

Example first. Amir's marketing head asks: "If we spend ₹12,000 on ads tomorrow, how many orders should we expect?" A straight line through the 20 days answers it: Orders ≈ 176.38 + 17.60 × AdSpend. For AdSpend 12 (thousand), that is about 388 orders. SLOPE and INTERCEPT give the two numbers; LINEST gives them plus the statistics that tell you how much to trust them. This lesson reads every cell of the LINEST output and shows the one trap that catches almost everyone with several X columns.

First look at the data (before → after):

Expected result — LINEST(E2:E21,B2:B21,TRUE,TRUE)

=SLOPE(E2:E21,B2:B21)   =INTERCEPT(E2:E21,B2:B21)
=LINEST(E2:E21,B2:B21,TRUE,TRUE)   (spills 5 rows × 2 columns)
=FORECAST.LINEAR(12,E2:E21,B2:B21)

Every cell of LINEST(E2:E21,B2:B21,TRUE,TRUE)

The arguments: LINEST(known_y's, known_x's, const, stats). const TRUE (or omitted) calculates the intercept normally; FALSE forces the line through 0, which is almost never right for business data. stats TRUE adds rows 2 to 5. Without it, LINEST returns only the first row.

Reading it in plain words

• Slope 17.60: on days with ₹1,000 more ad spend, orders were higher by about 17.60 on average.
• Intercept 176.38: the line's value at AdSpend 0. It is outside the data (the lowest spend was 5), so do not read it as "orders with no ads".
• Standard error of the slope 2.12: the slope is 8.31 times its standard error (the t statistic), far above 2, so the link is very unlikely to be chance.
• R² 0.7931: about 79% of the day-to-day variation in orders goes with ad spend; the rest is other things (discount, rain, chance). For one X column, R² = CORREL² (0.891² = 0.7931).
• sey 29.18: a typical prediction misses by about 29 orders. Same as =STEYX(E2:E21,B2:B21).
• F 69.01 with df 18: tests whether the whole line is better than just the average. =F.DIST.RT(69.01,1,18) gives a probability of 1.43E-07, so yes.
• Sums of squares: regression 58768.51 + residual 15329.29 = total 74097.80. R² = regression ÷ total.

Pick single cells from LINEST. =INDEX(LINEST(E2:E21,B2:B21,TRUE,TRUE),3,1) returns R² alone (0.7931). Microsoft documents =INDEX(LINEST(known_y's,known_x's),1) for the slope and …,2) for the intercept.

TREND gives the fitted line for many x values at once. =TREND(E2:E21,B2:B21) spills the 20 fitted values; =TREND(E2:E21,B2:B21,{10;12;14}) spills predictions for AdSpend 10, 12 and 14: 352.34, 387.53, 422.73.

The trap: several X columns come back in REVERSE order. With three X columns (AdSpend, Discount, Rain in B, C, D):

=LINEST(E2:E21,B2:D21,TRUE,TRUE)

The first coefficient belongs to the last X column. Microsoft's documentation states the order as {mn, mn-1, …, m1, b}. Label the output by hand every time. Rows 3 to 5 use only two columns; the other cells show #N/A, which is normal. Wrap the formula in IFERROR(…,"") to hide them.

Notice the slope changed. Alone, AdSpend has a slope of 17.60. With Discount and Rain in the model, it is 11.32. On campaign days Amir raised ads and discounts together, so the simple slope gave ads credit for part of the discount effect. Lesson 30.6 reads this three-X model line by line with the ToolPak.

Every number on this page was computed with Excel's documented formulas and re-checked twice: in LibreOffice Calc 25.2 with the same formulas, and in Python with scipy and statsmodels.

Microsoft sources (checked 7 October 2026): LINEST function.

Practice task (try it before you open the answer):

1. Use SLOPE and INTERCEPT to predict orders for AdSpend 9 (thousand).
2. What does =INDEX(LINEST(E2:E21,B2:B21,TRUE,TRUE),3,2) return, and what does it mean?
3. In the three-X LINEST, which cell holds the Discount coefficient?