LINEST Function in Microsoft Excel: Syntax, Examples & Troubleshooting
Fit Excel least-squares regression with LINEST. Read slope, intercept and the five-row statistics array, verify a noisy-data example and interpret reversed predictor order.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
Quick answer: LINEST fits a least-squares linear model and returns its coefficients. With one predictor, the first row contains slope, intercept. Set stats to TRUE for a five-row array of regression statistics, not a single correlation number.
Syntax and defaults
=LINEST(known_y's, [known_x's], [const], [stats])
Known y values are required. Omitted known x values default to 1, 2, 3, … of the same size. const TRUE or omitted estimates the intercept; FALSE fixes it at 0 and refits the slopes. stats FALSE or omitted returns coefficients only; TRUE returns additional statistics.
For multiple x variables, coefficients appear in reverse input-variable order, followed by the intercept: mn, …, m1, b. Label the output before interpreting it.
Worked example: fit data with real residuals
Enter A1:B5 as:
| Row | A · x | B · y |
|---|---|---|
| 1 | x | y |
| 2 | 1 | 3 |
| 3 | 2 | 5 |
| 4 | 3 | 4 |
| 5 | 4 | 8 |
- Enter
=LINEST(B2:B5,A2:A5,TRUE,FALSE)in D2, reserving D2:E2. - Expected output: 1.4, 1.5.
- The fitted equation is y = 1.4x + 1.5.
- At x = 5, the equation predicts 8.5; it does not guarantee an actual observation of 8.5.
The independent check uses x mean 2.5 and y mean 5. The centered cross-product sum is 7, and the centered x sum of squares is 5, so slope = 7/5 = 1.4 and intercept = 5−1.4×2.5 = 1.5.
Second example: the full statistics block
Enter =LINEST(B2:B5,A2:A5,TRUE,TRUE) in D5, reserving D5:E9. Expected approximate output is:
| Output row | First column | Second column |
|---|---|---|
| 1 | Slope: 1.4 | Intercept: 1.5 |
| 2 | Slope standard error: 0.64807407 | Intercept standard error: 1.77482393 |
| 3 | R²: 0.7 | y estimate standard error: 1.44913767 |
| 4 | F statistic: 4.66666667 | Degrees of freedom: 2 |
| 5 | Regression sum of squares: 9.8 | Residual sum of squares: 4.2 |
Predicted y values are 2.9, 4.3, 5.7 and 7.1. Squared residuals total 4.2; total centered y variation is 14. Thus regression variation is 9.8 and R² = 9.8/14 = 0.7. The F output is a statistic, not a p-value and not the probability returned by an F-test function.
Advanced application: multiple predictors and intercept constraints
For a separate dataset, put x1 values 0,1,0,1 in H2:H5, x2 values 0,0,1,1 in I2:I5, and y values 5,7,8,10 in J2:J5. =LINEST(J2:J5,H2:I5,TRUE,FALSE) returns expected coefficients 3, 2, 5. They describe y = 2×x1 + 3×x2 + 5; the first returned coefficient belongs to the last x column.
Forcing the original one-predictor fit through zero, =LINEST(B2:B5,A2:A5,FALSE,FALSE), yields expected 1.9, 0. It refits the slope using sum(xy)/sum(x²) = 57/30, rather than simply deleting the 1.5 intercept.
Microsoft documents that redundant predictors may be removed for collinearity, showing zero coefficients and zero standard errors; degrees of freedom and other statistics can then change. With const FALSE, the intercept standard error is documented as #N/A. Do not treat these outputs as ordinary nonzero estimates or infer causation from a high R².
Array entry and data-shape checks
In a current Microsoft 365 build with dynamic arrays, enter the formula in the top-left output cell and press Enter, leaving enough empty cells for its result. In a legacy array workflow, select the intended output range first and confirm the formula with Ctrl+Shift+Enter. Excel inserts the braces; do not type them manually.
Keep each observed y paired with the corresponding x row. A single predictor requires matching x/y dimensions. For multiple predictors, a column of y observations uses one x column per predictor; a row of y observations uses one x row per predictor. New prediction inputs must retain the same predictor layout. Headers are not observations.
A blocked dynamic-array destination can produce #SPILL!, while incomplete legacy output selection can hide results. The reviewed function page does not provide a complete invalid-shape/error/coercion table, so no cross-application error behavior is assumed here. Inspect missing, text and erroneous source values rather than silently replacing them with zeros.
Compatibility and verification
Microsoft’s reference checked on October 17, 2026 lists: Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel 2024 Excel 2024 for Mac Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2016. This current support list does not establish the first release. The introduction version and last functioning version have not been established from the reviewed documentation. No retirement is asserted.
Example verification: Expected numerical results are independently calculated; text and shape expectations follow the documented contract. These examples have not been tested in Microsoft Excel. No screenshot or native execution is claimed.
Related guides and source
SLOPE in Excel | INTERCEPT in Excel | RSQ in Excel | INDEX in Excel
- Microsoft LINEST reference, retrieved October 17, 2026.