# Excel LINEST Function: Syntax, Output and Examples

The Excel LINEST function calculates the statistics for a straight line that best fits your data using the least squares method, then returns an array describing that line [1]. In practice you use it to get a slope and an intercept you can plug into $y = mx + b$. This article covers the syntax, the order of the returned values, and worked examples you can copy into your own sheet.

## Quick Answer

- LINEST returns an array, so the order of its output matters more than the formula itself. For one x variable the first row is the slope then the intercept, written as $\{m, b\}$ [1].
- The full syntax is `LINEST(known_y's, [known_x's], [const], [stats])` [1].
- Set `stats` to TRUE to get the extra regression statistics. Set it to FALSE or omit it to get only the coefficients [1].
- Because it returns multiple values, enter it as an array formula or wrap it in `INDEX` to pull out a single number [1].
- For a simple two-variable fit, `SLOPE`, `INTERCEPT` and `RSQ` give the same key numbers with less fuss [2].

## Syntax

`LINEST(known_y's, [known_x's], [const], [stats])` [1]

| Argument | Required? | Meaning |
|---|---|---|
| `known_y's` | Required | The set of y-values you already know in the relationship $y = mx + b$ [1]. |
| `known_x's` | Optional | The set of x-values. If omitted, Excel assumes the array $\{1,2,3,...\}$ the same size as `known_y's` [1]. |
| `const` | Optional | TRUE or omitted lets Excel calculate the intercept $b$ normally. FALSE forces $b$ to equal 0 [1]. |
| `stats` | Optional | TRUE returns the additional regression statistics. FALSE or omitted returns only the coefficients [1]. |

The shape of your input ranges controls how Excel reads the variables. If `known_y's` sits in a single column, each column of `known_x's` is treated as a separate variable. If `known_y's` sits in a single row, each row of `known_x's` is a separate variable [1].

## How It Works

LINEST fits the line by least squares, which means it chooses the slope and intercept that minimize the sum of the squared differences between your observed y-values and the y-values the line predicts [1]. Those squared differences are the residuals, and their total is the basis for the goodness-of-fit statistics [3].

The output array is ordered from the last coefficient to the first. For a single x variable it is $\{m, b\}$, so the slope comes first and the intercept second [1]. With more than one x variable the first row becomes $\{m_n, m_{n-1}, ..., m_1, b\}$, one coefficient per x variable plus the constant [1].

When you set `stats` to TRUE, LINEST returns five rows. The first row holds the coefficients. The second row holds the standard errors for those coefficients. The third row holds the coefficient of determination ($R^2$) and the standard error of the estimate. The fourth row holds the F statistic and the degrees of freedom. The fifth row holds the regression sum of squares and the residual sum of squares [1].

That layout is why a bare `LINEST` formula looks confusing. The values are correct, but they spill across cells in an order you have to know in advance.

## Worked Example

The dataset below is ten months of sales figures, with month as the x variable and sales as the y variable. Columns E, F and G hold the three LINEST results, and columns C and D use them to build fitted values and residuals.

| Row | A (Month) | B (Sales) | C (Fitted) | D (Residual) | E (Slope) | F (Intercept) | G (R-squared) |
|---|---|---|---|---|---|---|---|
| 1 | Month | Sales | Fitted | Residual | Slope | Intercept | R-squared |
| 2 | 1 | 120 | `=$E$2*A2+$F$2` -> 121.182 | `=B2-C2` -> -1.18182 | `=INDEX(LINEST(B2:B11,A2:A11,TRUE,TRUE),1,1)` -> 13.85 | `=INDEX(LINEST(B2:B11,A2:A11,TRUE,TRUE),1,2)` -> 107.33 | `=INDEX(LINEST(B2:B11,A2:A11,TRUE,TRUE),3,1)` -> 0.999583 |
| 3 | 2 | 135 | `=$E$2*A3+$F$2` -> 135.03 | `=B3-C3` -> -0.030303 | | | |
| 4 | 3 | 150 | `=$E$2*A4+$F$2` -> 148.879 | `=B4-C4` -> 1.12121 | | | |
| 5 | 4 | 162 | `=$E$2*A5+$F$2` -> 162.727 | `=B5-C5` -> -0.727273 | | | |
| 6 | 5 | 178 | `=$E$2*A6+$F$2` -> 176.576 | `=B6-C6` -> 1.42424 | | | |
| 7 | 6 | 190 | `=$E$2*A7+$F$2` -> 190.424 | `=B7-C7` -> -0.424242 | | | |
| 8 | 7 | 205 | `=$E$2*A8+$F$2` -> 204.273 | `=B8-C8` -> 0.727273 | | | |
| 9 | 8 | 218 | `=$E$2*A9+$F$2` -> 218.121 | `=B9-C9` -> -0.121212 | | | |
| 10 | 9 | 232 | `=$E$2*A10+$F$2` -> 231.97 | `=B10-C10` -> 0.030303 | | | |
| 11 | 10 | 245 | `=$E$2*A11+$F$2` -> 245.818 | `=B11-C11` -> -0.818182 | | | |

The three key cells are E2, F2 and G2. Each one wraps LINEST in `INDEX` so a single value comes out instead of an array.

- **E2** returns the slope, 13.85. Sales rise by about 13.85 units per month.
- **F2** returns the intercept, 107.33. That is the fitted sales value when month equals 0.
- **G2** returns $R^2$, 0.999583. The line explains almost all the variation in sales.

Column C applies the fitted equation $y = 13.85x + 107.33$. Column D subtracts the fitted value from the observed value to give the residual. The residuals are small and change sign, staying within about 1.5 units of zero, which is what a good straight-line fit looks like. Even so, a near-perfect $R^2$ does not prove the straight line is the right shape for the data, only that it tracks the points closely over this range.

If you want to pull values by position from any array, the Excel INDEX function is the standard companion to LINEST.

## More Examples

**Coefficients only.** `=LINEST(B2:B11,A2:A11,1,0)` returns just the slope and intercept, with no statistics. This is the form to use when you only need the equation [2].

**Force the intercept to zero.** `=LINEST(B2:B11,A2:A11,FALSE,TRUE)` fits the line through the origin. Only do this when theory says the intercept must be zero, because forcing it changes every other statistic.

**Multiple regression.** With two x variables in columns A and B and y in column C, `=LINEST(C2:C11,A2:B11,TRUE,TRUE)` returns coefficients in the order $\{m_2, m_1, b\}$, so the coefficient for the last x column comes first [1].

**Predict a new value.** Once you have the slope and intercept, a forecast is just arithmetic. `=$E$2*12+$F$2` gives the fitted sales figure for month 12.

**Check the fit with the F statistic.** Compare the F value LINEST returns against the critical value from `FINV` at your chosen significance level, using the degrees of freedom LINEST reports [3].

## Errors and How to Fix Them

| Error or symptom | Cause | Fix |
|---|---|---|
| `#VALUE!` | A text value or empty cell sits inside a range you passed [3]. | Clean the ranges so every cell holds a number. |
| Only one number appears | The formula was entered normally instead of as an array formula [1]. | Enter it as an array formula, or wrap it in `INDEX` to request one element. |
| `#SPILL!` | In Microsoft 365 or Excel 2021, cells in the spill range already hold data. | Clear the cells so the full array has room. |
| Coefficients look swapped | You read the array left to right as written. | Remember the order is $\{m_n, ..., m_1, b\}$, last coefficient first [1]. |
| `#REF!` | `known_y's` and `known_x's` have different sizes. | Make both ranges the same number of rows or columns. |

## Common Mistakes

- **Reading the slope and intercept in the wrong order.** LINEST returns the slope first and the intercept last [1]. If your numbers look backwards, this is almost always why.
- **Forgetting the array entry.** A plain Enter on a multi-cell LINEST formula returns only the first value [1]. Wrap it in `INDEX` or commit it as an array formula.
- **Leaving `stats` out and expecting statistics.** The default is FALSE, so you get coefficients only [1]. Pass TRUE when you want $R^2$, standard errors and the F statistic.
- **Setting `const` to FALSE by habit.** That forces the intercept to zero and changes the slope [1]. Leave it TRUE unless zero is genuinely required.
- **Treating a high $R^2$ as proof of a good model.** A high $R^2$ can still hide curvature or a pattern in the residuals, so plot the residuals as well. Fit quality and model correctness are different questions.
- **Mixing up rows and columns of x.** The orientation of `known_y's` decides whether columns or rows of `known_x's` are separate variables [1]. A transposed range silently changes the model.

## Limitations

LINEST fits models that are linear in the unknown parameters. That covers straight lines, polynomials, and transformations such as logarithmic, exponential and power series, but it does not fit arbitrary nonlinear models directly [1]. You have to transform the data first.

The function also returns numbers without interpretation. It will happily report a slope, an intercept and a strong $R^2$ for data where a straight line is the wrong shape, where the residuals are correlated, or where one point dominates the fit. The statistics describe how well the line fits the sample you gave it, not whether that line is the right model. For routine two-variable work, the individual `SLOPE`, `INTERCEPT`, `RSQ` and `STEYX` functions return the same key results with less array handling [2].

## Frequently Asked Questions

### What order does LINEST return its values in?

For a single x variable the array is $\{m, b\}$, slope first and intercept second [1]. With multiple x variables the first row is $\{m_n, m_{n-1}, ..., m_1, b\}$, so the coefficient for the last x column appears first [1]. When `stats` is TRUE, four more rows follow below the coefficients.

### Why does my LINEST formula return only one number?

You entered it as a normal formula. LINEST returns an array, so it must be entered as an array formula, or you can wrap it in `INDEX` to extract one element at a time [1]. The `INDEX` approach is usually easier because it needs no special keystroke.

### How do I get just the slope and intercept?

Use `=LINEST(known_y's, known_x's, TRUE, FALSE)`, which returns only the coefficients [2]. Alternatively, `SLOPE` and `INTERCEPT` return those two values directly for a two-variable fit [2].

### What does the third row of LINEST output contain?

The third row holds the coefficient of determination, $R^2$, in the leftmost cell and the standard error of the estimate next to it [1]. $R^2$ tells you the share of variation in y explained by the fitted line.

### Can LINEST handle more than one x variable?

Yes. Supply a range with one column or row per x variable, and LINEST returns one coefficient for each plus the constant [1]. The output grows one column wider for each extra x variable, and rows three to five still hold their statistics in the first two columns, with #N/A in the remaining cells.

### Is LINEST the same as the Regression tool in the Data Analysis add-in?

They fit the same least squares model. The add-in produces a formatted report, while LINEST returns raw values you can reference in other formulas [2]. Many analysts use LINEST when they need the coefficients to feed further calculations, and the add-in when they want a readable summary.

## References

1. [LINEST function | Microsoft Support](https://support.microsoft.com/en-us/excel/functions/linest-function)
2. [EXCEL 2007: Two-Variable Regression using function LINEST](https://cameron.econ.ucdavis.edu/excel/ex54regressionwithlinest.html)
3. [LINEST](https://www.mit.edu/~mbarker/formula1/f1help/04-g-m60.htm)

## Further Reading

- [Broman KW, Woo KH (2018). Data Organization in Spreadsheets. The American Statistician](https://doi.org/10.1080/00031305.2017.1375989)
- [Ziemann M, Eren Y, El-Osta A (2016). Gene name errors are widespread in the scientific literature. Genome Biology](https://doi.org/10.1186/s13059-016-1044-7)
- [Microsoft Support: Excel help and learning](https://support.microsoft.com/en-us/excel)

## Related Articles

- [Excel TEXT Function: Syntax, Format Codes and Examples](/blog/data-analysis/excel-text-function)
- [Excel SUBTOTAL Function: Syntax, Formulas and Examples](/blog/data-analysis/excel-subtotal-function-formula)
- [Excel FIND Function: Syntax, Examples and SEARCH Comparison](/blog/data-analysis/excel-find-function-syntax-examples)
- [COUNTIF Function in Excel: Syntax and Examples](/blog/data-analysis/countif-function-excel)
- [Excel INDIRECT Function: Syntax and Examples](/blog/data-analysis/excel-indirect-function)