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
known_y: The dependent values: what you want to explain or predict.known_x(optional): The independent values; several columns for several variables. Default 1, 2, 3, …const(optional): FALSE forces the line through zero. Default TRUE.stats(optional): TRUE adds four rows of statistics under the coefficients. Default FALSE.
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
Slope and intercept
A B C D E 1 Ad spend Sales 2 10 120 5.18 69.40 3 15 150 4 20 168 5 25 205 6 30 222 In D2:
=LINEST(B2:B6, A2:A6)→ 5.18, 69.40The full statistics table
A B C D E 1 Ad spend Sales 2 10 120 5.18 69.4 3 15 150 0.326 6.924 4 20 168 0.988 5.161 5 25 205 251.869 3 6 30 222 6708.1 79.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.9Rows: slope | intercept, their standard errors, R² | standard error of y, F | degrees of freedom, regression | residual sum of squares.
One value from the table
A B C D 1 Ad spend Sales 2 10 120 0.988 3 15 150 4 20 168 5 25 205 6 30 222 In D2:
=INDEX(LINEST(B2:B6, A2:A6, TRUE, TRUE), 3, 1)→ 0.988Row 3, column 1 is R².
Common mistakes
- Expecting the slopes in column order. With x columns A, B and C, LINEST returns the slope for C first, then B, then A, then the intercept.
- Leaving no room to spill. With statistics, LINEST needs five rows and one column per x variable plus one. Clear that space first.
- Swapping y and x. known_y comes first: the values you want to explain.
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.
- Czech: LINREGRESE
- Danish: LINREGR
- Dutch: LIJNSCH
- Finnish: LINREGR
- French: DROITEREG
- German: RGP
- Hungarian: LIN.ILL
- Italian: REGR.LIN
- Norwegian: RETTLINJE
- Polish: REGLINP
- Portuguese (Brazil): PROJ.LIN
- Portuguese (Portugal): PROJ.LIN
- Russian: ЛИНЕЙН
- Spanish: ESTIMACION.LINEAL
- Swedish: REGR
- Turkish: DOT
Practise it
Related functions
- SLOPE: The slope of the straight line that best fits the data: how much y changes for each 1 added to x.
- INTERCEPT: Where the best-fit straight line crosses the y axis: the predicted y when x is 0.
- RSQ: R squared: the share of the variation in y that the best-fit line explains, from 0 to 1.
- TREND: Values on the best-fit straight line (least squares): fitted values for the known data, or predictions for new x values. Spills.
- STEYX: The standard error of the regression: the typical distance of a y value from the best-fit line.