Quick Navigation
LINEST Function
Summary
The LINEST function performs linear regression analysis using the least squares method to find the best-fitting straight line through your data points. It returns an array containing slope coefficients, intercept, and optional comprehensive regression statistics for advanced data modeling and forecasting.
Syntax
LINEST(known_y's, [known_x's], [const], [stats])Parameters
| Parameter | Type | Required | Description |
|---|---|---|---|
| known_y's | Range/Array |
Yes | Set of dependent y-values for regression analysis |
| known_x's | Range/Array |
No | Independent x-value sets; omitted assumes sequential indexing |
| const | Boolean |
No | Determines if constant b=0 (FALSE) or calculated normally (TRUE) |
| stats | Boolean |
No | Includes standard errors, R², F-statistic, and sums of squares |
Using the LINEST Function
LINEST is the cornerstone of linear regression in Excel, enabling analysts to quantify relationships between variables, validate model fit through R² and F-tests, and generate predictions. Enter as array formula (Ctrl+Shift+Enter in older Excel) or dynamic array in Excel 365 to capture the full statistics matrix.
Common LINEST Examples
Simple Linear Regression (Slope & Intercept)
=LINEST(B2:B10, A2:A10)Select 2x1 range, array-enter to get slope (top-left) and y-intercept (top-right). Predicts y from single x variable.
Sales Forecast with LINEST
=SUM(LINEST(B2:B7,A2:A7)*{9,1})Forecasts 9th month sales using first 6 months data. Multiplies coefficients by {month,1} for prediction.
Multiple Regression Statistics
=LINEST(D2:D12,A2:C12,TRUE,TRUE)Full regression output for property valuation using 3 predictors (space, offices, entrances). Analyzes R²=0.996.
Extract Specific Statistic
=INDEX(LINEST(B2:B10,A2:A10,TRUE,TRUE),3,2)Returns R² value (row 3, column 2) to measure model fit quality.
Frequently Asked Questions
Common Errors and Solutions
#VALUE!
Cause: known_y's and known_x's have different dimensions or contain text
Solution: Ensure matching array sizes; clean data of non-numeric values
#N/A in intercept statistics
Cause: const=FALSE eliminates intercept calculation
Solution: Expected behavior; use INDEX(array, row, column) to access available stats
Single values returned
Cause: Not entered as array formula
Solution: Select full output range first (5x2 for stats=TRUE), then Ctrl+Shift+Enter
Inconsistent results vs SLOPE
Cause: Collinear or degenerate data
Solution: LINEST more robust; handles edge cases where SLOPE returns #DIV/0!
Notes
- Must select output range before entering formula
- Output array: {slopes,intercept; se_slopes,se_b; R²,sey; F,df; ssreg,ssresid}
- For polynomials: LINEST(yvals, {x,x²,x³}) or use COLUMN trick
- Collinearity auto-detected; redundant X columns show 0 coef/se
- F-test uses v1=n-df-1, v2=df degrees of freedom
- Separator: comma (row), semicolon (column) in array constants
Compatibility
Available in: Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019, Excel 2021, Excel 365
Not available in:
Content last reviewed: December 9, 2025
Update frequency: As needed
Excel versions tested: Excel 2007+