LINEST Function
Returns the coefficients and statistics of a least-squares straight-line fit.
Category
Statistical
Available in
All versions; spills in Microsoft 365 and Excel 2021+ (older versions: select the output cells and press Ctrl+Shift+Enter)
Returns
An array
Overview
LINEST fits y = m × x + b (or, with several x columns, y = m₁x₁ + m₂x₂ + … + b) and returns the coefficients as an array. With one x, the first row is the slope then the intercept.
Set stats to TRUE for a five-row block of regression statistics: standard errors, R², the standard error of y, the F statistic and degrees of freedom, and the regression and residual sums of squares.
With several x columns the coefficients come back in reverse order — the last x column's slope first, the intercept last.
Syntax
Arguments
| Argument | Required? | What it does |
|---|---|---|
| known_y's | Yes | The y values. |
| known_x's | No | One or more columns of x values. If left out, 1, 2, 3 … is used. |
| const | No | TRUE or omitted calculates the intercept; FALSE forces it to 0. |
| stats | No | TRUE returns the full statistics block; FALSE or omitted returns only the coefficients. |
Examples
Example 1 — Full regression output
One formula in E2 spills ten results, laid out as Excel always arranges them.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Month | Ad spend ($k) | Sales ($k) | Slope (m) | Intercept (b) | |
| 2 | Jan | 2 | 21 | Coefficient | 4.1071 | 12.2143 |
| 3 | Feb | 3 | 25 | Std error | 0.2067 | 1.1035 |
| 4 | Mar | 5 | 33 | R² | Std err y | 0.9850 | 1.0938 |
| 5 | Apr | 4 | 28 | F | df | 394.78 | 6 |
| 6 | May | 6 | 38 | SS reg | SS resid | 472.32 | 7.18 |
| 7 | Jun | 7 | 40 | |||
| 8 | Jul | 8 | 46 | |||
| 9 | Aug | 5 | 31 |
Slope 4.11 with a standard error of 0.21, R² 0.985 and a large F statistic: the relationship is very unlikely to be chance.
Example 2 — Pick out one number
Wrap it in INDEX to return a single value.
Returns the slope, 4.1071. INDEX(LINEST(C2:C9,B2:B9),2) returns the intercept, and INDEX(LINEST(C2:C9,B2:B9,TRUE,TRUE),3,1) returns R².
Example 3 — Two drivers at once
With ad spend in B and price in C and sales in D, both x columns go in one range.
The first row returns the price coefficient, then the ad-spend coefficient, then the intercept.
Common mistakes
- #SPILL! Something is in the way of the output block. Clear the cells to the right and below.
- Reversed order with several x columns. The coefficients are listed from the last x column to the first.
- #VALUE! A cell in the y or x range is empty or holds text. LINEST needs a complete block of numbers.
Notes

Author
Kyle Stott
Certified Microsoft Excel Expert
Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.