Excel functions / Statistics

LINEST function

Fits a straight line by least squares and returns its slope(s) and intercept, with the full regression statistics (standard errors, R², F, sums of squares) if you ask. Spills.

Syntax

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

Arguments

When to use it

LINEST fits a straight line by least squares and returns its coefficients: the slope (or several slopes, one per x column) and the intercept. Ask for the statistics with a fourth argument of TRUE and it spills a five-row table with the standard errors, R², the F statistic, degrees of freedom and sums of squares.

That table is what the Analysis ToolPak's Regression tool reports, but LINEST is a formula, so it updates when the data change. With several x columns, the slopes come back in reverse order of the columns, then the intercept.

Examples

  1. Slope and intercept

    ABCDE
    1Ad spendSales
    2101205.1869.40
    315150
    420168
    525205
    630222

    In D2: =LINEST(B2:B6, A2:A6) → 5.18, 69.40

    Try this example

  2. The full statistics table

    ABCDE
    1Ad spendSales
    2101205.1869.4
    3151500.3266.924
    4201680.9885.161
    525205251.8693
    6302226708.179.9

    In D2: =LINEST(B2:B6, A2:A6, TRUE, TRUE) → 5.18, 69.4; 0.326, 6.924; 0.988, 5.161; 251.869, 3; 6708.1, 79.9

    Rows: slope | intercept, their standard errors, R² | standard error of y, F | degrees of freedom, regression | residual sum of squares.

    Try this example

  3. One value from the table

    ABCD
    1Ad spendSales
    2101200.988
    315150
    420168
    525205
    630222

    In D2: =INDEX(LINEST(B2:B6, A2:A6, TRUE, TRUE), 3, 1) → 0.988

    Row 3, column 1 is R².

    Try this example

Common mistakes

Set const to FALSE to force the line through zero. For a single slope or intercept, SLOPE and INTERCEPT are easier to read.

LINEST in other languages

Excel shows function names in its own language. In these languages it also puts a semicolon between arguments where English has a comma.

Translate a whole formula

Try LINEST in the playground

Practise it

Related functions