LINEST function in Excel

SLOPE and INTERCEPT give you two numbers. LINEST gives you the entire regression in one spilled array, including the numbers that tell you whether to believe it. Reading a slope and doing statistics are different jobs.

LINEST(known_ys, [known_xs], [const], [stats])

What does LINEST return, and how do you read it?

With stats set to TRUE, LINEST returns a five-row block. For a simple one-predictor regression it's two columns wide, and every cell has a name worth knowing:

        col 1                  col 2
row 1   slope (m)              intercept (b)
row 2   se of slope            se of intercept
row 3   R²                     se of the y estimate
row 4   F statistic            degrees of freedom
row 5   regression SS          residual SS

Here's the block for the footfall example, the eight points computed in Worked example 2:

        2.5524       14.6398        (slope, intercept)
        0.0896        2.1034        (standard errors)
        0.9927        1.7965        (R², std error of estimate)
      811.2022        6             (F, degrees of freedom)
     2618.14         19.36          (regression SS, residual SS)

Read it as a diagnostic panel. The slope is 2.55, and the standard error of the slope (0.09) tells you how tightly it's pinned down. At two standard errors the slope is roughly 2.55 ± 0.18, comfortably away from zero, so the relationship is statistically real for this sample. R² = 0.9927 says 99.3% of the variation in sales is explained by footfall here. The standard error of the estimate (1.80, in ₹ thousands) is the typical size of a prediction miss. The F-statistic of 811, with 6 degrees of freedom, tests the whole model against "no relationship". A large F relative to its distribution means the fit isn't chance. To pull a single number out of the spilled block, wrap it in INDEX. =INDEX(LINEST(y, x, TRUE, TRUE), 1, 1) is the slope, (1, 2) the intercept and (3, 1) the R².

How do RSQ and CORREL relate to LINEST?

RSQ, CORREL, and the identity. CORREL(x, y) is the Pearson correlation coefficient r. It runs from −1 to +1 and measures the direction and tightness of the linear co-movement. RSQ(known_ys, known_xs) is r², the fraction of variance explained, which runs from 0 to 1. For a simple one-x regression they satisfy the exact identity R² = CORREL². Our CORREL is 0.9963, and 0.9963² = 0.9927, the same figure as the RSQ and the LINEST R². Once you know this, you can sanity-check any of the three against the others in your head.

Multiple predictors bring a gotcha of their own. With more than one x, LINEST returns the coefficients in reverse order: rightmost predictor first, intercept last. It catches everyone the first time. And RSQ/CORREL only accept a single x each, so a multi-predictor R² has to come from LINEST row 3. UI: in M365 LINEST spills automatically, so you just select one cell and type, then press Enter. On legacy Excel you had to pre-select the full five-row block and commit with Ctrl+Shift+Enter. If you'd prefer a table to a spilled array, the Data ▸ Data Analysis ▸ Regression tool (Analysis ToolPak add-in) produces the same statistics as a formatted report.