Excel LINEST Function: Syntax, Output and Examples

By Dr. Zubair Khalid, DVM, MS, PhD · 2026-10-03

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]

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.

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

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
2. EXCEL 2007: Two-Variable Regression using function LINEST
3. LINEST

Further Reading

• Broman KW, Woo KH (2018). Data Organization in Spreadsheets. The American Statistician
• Ziemann M, Eren Y, El-Osta A (2016). Gene name errors are widespread in the scientific literature. Genome Biology
• Microsoft Support: Excel help and learning

Related Articles

• Excel TEXT Function: Syntax, Format Codes and Examples
• Excel SUBTOTAL Function: Syntax, Formulas and Examples
• Excel FIND Function: Syntax, Examples and SEARCH Comparison
• COUNTIF Function in Excel: Syntax and Examples
• Excel INDIRECT Function: Syntax and Examples