LINEST function
LINEST runs a least-squares regression and returns an array with the slope or slopes and the intercept of the best-fit line, plus optional statistics. It allows multiple x columns.
Example
Read it as a sentence: fit a straight line of the G1:G5 values against the F1:F5 values and return the slope, which gives 0.6.
Syntax
LINEST(known_y's, [known_x's], [const], [stats])
Arguments in [square brackets] are optional.
-
known_y's(required) - Known y values.
-
known_x's(optional) - Known x values; one or more columns.
-
const(optional) - TRUE (default) calculates the intercept; FALSE forces it to 0.
-
stats(optional) - TRUE returns extra regression statistics.
Returns: An array with the slope(s) and intercept of the best-fit line, plus optional statistics.
One LINEST formula can fill more than one cell. When the result has several values, they spill into the cells below or to the right of the formula.
What Excel actually returns
Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.
| Formula | Excel shows | Case |
|---|---|---|
=INDEX(LINEST({2;4;5;4;5},{1;2;3;4;5}),1,1) | 0.6 | INDEX slope |
=LINEST(Data!G1:G5,Data!F1:F5) | 0.6 | Full result (spills) |
=LINEST(Data!G1:G5) | 0.6 | Y only |
=LINEST(Data!G1:G5,Data!F1:F5,FALSE) | 1.2 | Const FALSE |
=LINEST(Data!G1:G5,Data!F1:F5,TRUE,TRUE) | 0.6 | Stats TRUE (spills 5x2) |
=LINEST(Data!G1:G5,Data!J1:J5) | 0 | Constant x |
=LINEST(Data!G1:G5,Data!K1:K3) | #REF! | Different sizes |
=LINEST(Data!B1:B5,Data!F1:F5) | #VALUE! | Text in y |
The Data sheet these formulas read
A cell that starts with = holds that formula.
Watch out for
- Coefficients come out in reverse order of the x columns, intercept last.
- Spills in 365; older versions need Ctrl+Shift+Enter.
- Use INDEX to pull a single value.
Where it works
- Excel
- LINEST is in every version of Excel from 2007 on.
- Google Sheets
- Same name, same behaviour for everyday use.
Part of Statistical functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .