Excel LINEST Function

last modified October 5, 2026

The LINEST function calculates the statistics for a straight line that best fits a set of data using the method of least squares. Unlike SLOPE or INTERCEPT, it can describe a simple line or a multiple regression with several independent variables, and it can return a full set of regression statistics. This tutorial provides a comprehensive guide to using LINEST with detailed examples. You'll learn the basic syntax, the reversed coefficient order, the layout of the statistics array, and how LINEST compares with related functions.

LINEST Function Basics

LINEST fits the equation y = m1*x1 + m2*x2 + ... + b to your data. With one independent variable the equation reduces to y = m*x + b, where m is the slope and b is the intercept. Because the function returns an array of values, it behaves like an array formula.

Component	Description
Function Name	LINEST
Syntax	=LINEST(known_ys, [known_xs], [const], [stats])
Arguments	Required: known_ys Optional: known_xs, const, stats
Return Value	An array of coefficients, and optionally regression statistics

This table breaks down the essential components of the LINEST function. It shows the function name, the syntax format, the argument requirements, and the value it returns.

Argument	Meaning
known_ys	The dependent y-values you already know. This is the range you are trying to model, and it comes first in the argument list.
known_xs	Optional. One or more sets of independent x-values. If it is omitted, LINEST uses the array {1,2,3,...} the same size as known_ys. With one variable the ranges may be any shape; with several variables known_ys must be a single row or column.
const	Optional logical value. If TRUE or omitted, the intercept b is calculated normally. If FALSE, b is forced to 0 and the line passes through the origin.
stats	Optional logical value. If TRUE, the function also returns the additional regression statistics. If FALSE or omitted, only the coefficients and the intercept are returned.

The argument order matters: the dependent known_ys range comes first, followed by the independent known_xs range. This is the opposite of the order used by many chart tools. If the two ranges do not have the same number of data points, LINEST returns the #REF! error.

Simple Linear Regression

This example demonstrates basic linear regression with one independent variable. The x-values are 1 through 4 and the y-values are 3, 5, 7, and 9, a perfect line with the equation y = 2x + 1.

A (x)	B (y)
1	3
2	5
3	7
4	9

We'll use LINEST to calculate the slope and intercept of this line.

=LINEST(B1:B4, A1:A4)

The result is the array {2, 1}. The first cell holds the slope (2) and the second holds the intercept (1). Notice that this is the reverse of the order in the equation y = mx + b, where the intercept is written last. To pull the two values out as ordinary numbers, use =INDEX(LINEST(B1:B4,A1:A4),1) for the slope and =INDEX(LINEST(B1:B4,A1:A4),2) for the intercept.

The Reversed Coefficient Order

For a multiple regression, LINEST returns one coefficient for each x column, followed by the intercept. The equation is written y = m1*x1 + m2*x2 + ... + b, but the returned array is {mn, ..., m2, m1, b}: the coefficient for the last x column appears first. Microsoft documents this as the array {mn,mn-1,...,m1,b}.

This reversal is the most common source of confusion. When the x columns are listed in the order x1, x2, x3, the output reads m3, m2, m1, b. If you build a polynomial fit by listing x, then x squared, then x cubed, the returned coefficients come back as the cubic term first and the linear term last, right before the intercept.

Multiple Regression

LINEST can handle multiple independent variables. This example uses two predictors, x1 and x2, and a response y that follows y = 1*x1 + 2*x2 + 1.

A (x1)	B (x2)	C (y)
1	2	6
2	3	9
3	5	14
4	7	19

The predictors occupy A1:B4 and the response occupies C1:C4.

=LINEST(C1:C4, A1:B4)

The result is the array {2, 1, 1}. Reading it in the returned order, the coefficient for x2 (the last x column) is 2, the coefficient for x1 is 1, and the intercept is 1. This confirms the equation y = 1*x1 + 2*x2 + 1 and shows how LINEST handles several predictors at once.

LINEST with Statistics

Setting the fourth argument to TRUE makes LINEST return a five-row array of regression statistics in addition to the coefficients. This example uses x-values 1 through 4 and y-values with a little noise: 3.1, 4.9, 7.2, and 8.8.

A (x)	B (y)
1	3.1
2	4.9
3	7.2
4	8.8

=LINEST(B1:B4, A1:A4, TRUE, TRUE)

With one x variable the result is a 5-by-2 array. The table below shows the layout of that array and the values for this dataset.

Row	Left column	Right column
1	slope m1 = 1.94	intercept b = 1.15
2	standard error of m1 = 0.0906	standard error of b = 0.2480
3	coefficient of determination R² = 0.9957	standard error of y = 0.2025
4	F statistic = 458.98	degrees of freedom = 2
5	regression sum of squares = 18.818	residual sum of squares = 0.082

The slope is 1.94 and the intercept is 1.15, so the fitted line is y = 1.94x + 1.15. The coefficient of determination is about 0.996, meaning the line explains roughly 99.6 percent of the variation in y. When const is FALSE, the intercept is 0 and its standard error is #N/A.

Forcing the Intercept to Zero

Sometimes a regression line must pass through the origin. The const argument forces the intercept to 0 and adjusts the slope to fit y = mx.

A (x)	B (y)
1	2
2	4
3	6
4	8

=LINEST(B1:B4, A1:A4, FALSE)

The result is the slope 2 for the line y = 2x. The FALSE argument is what fixes the intercept at 0 and adjusts the slope to fit y = mx.

Polynomial Regression

LINEST can fit a polynomial by supplying powers of x as separate columns. This example fits a quadratic curve to data that follows y = x² + 2x + 3. Column A holds x, column B holds x squared, and column C holds y.

A (x)	B (x²)	C (y)
1	1	6
2	4	11
3	9	18
4	16	27

=LINEST(C1:C4, A1:B4)

The result is the array {1, 2, 3}. Following the reversed coefficient order, the first value is the coefficient for x squared (1), the second is the coefficient for x (2), and the third is the intercept (3). This is exactly the curve y = x² + 2x + 3.

LINEST with Dynamic Arrays

In Excel for Microsoft 365 and Excel 2021, LINEST spills its result automatically. Enter the formula in a single cell and the array fills the neighboring cells. In Excel 2019 and earlier, you must select the whole output range and confirm the formula with Ctrl+Shift+Enter.

A (x)	B (y)
1	3
2	5
3	7

=LINEST(B1:B3, A1:A3)

In modern Excel this single formula spills the slope and intercept into two adjacent cells. If the spill range is blocked by other data, Excel shows a #SPILL! error until the blocking cells are cleared.

LINEST vs SLOPE, INTERCEPT, and TREND

Several Excel functions expose pieces of the same least-squares line. SLOPE and INTERCEPT return single numbers but accept only one x variable. TREND returns predicted y-values along the fitted line. LINEST is the most general of the group: it handles multiple predictors and returns the coefficients plus optional statistics.

Function	Returns
LINEST	An array: all coefficients and the intercept, plus statistics when stats is TRUE
SLOPE	The slope of a single-variable line
INTERCEPT	The intercept of a single-variable line
TREND	One or more predicted y-values along the fitted line
FORECAST / FORECAST.LINEAR	A single predicted y-value for a given x

For the data in the first example, x = 1 to 4 and y = 3, 5, 7, 9, the related functions agree with LINEST.

=LINEST(B1:B4, A1:A4)            ' {2, 1}
=SLOPE(B1:B4, A1:A4)             ' 2
=INTERCEPT(B1:B4, A1:A4)         ' 1
=TREND(B1:B4, A1:A4, 5)          ' 11

The slope is 2 and the intercept is 1, so the line predicts y = 2*5 + 1 = 11 at x = 5. Note that SLOPE, INTERCEPT, and TREND all expect the y-range before the x-range, just like FORECAST and LINEST.

LINEST Errors and Troubleshooting

Cause	Symptom	Fix
known_ys and known_xs have a different number of data points	#REF! error	Make both ranges the same size
A range contains blank, text, or logical values	Wrong coefficients or a #VALUE! error	Use contiguous numeric cells only
All known_xs values are identical (zero variance)	#DIV/0! error	Include x-values that actually vary
Two or more x columns are collinear	A coefficient of 0 with a standard error of 0	Remove the redundant x column
const is FALSE and stats is TRUE	The intercept is 0 and its standard error is #N/A	This is expected; read only the slope
The result fills only one cell in Excel 2019 or earlier	Only the first value is shown	Select the full output range and press Ctrl+Shift+Enter
The spill range is blocked in Excel 365 or 2021	#SPILL! error	Clear the cells that block the spill range
A referenced cell already holds an error	That same error is returned	Fix the source error first

The LINEST function turns a set of x-y pairs into a complete description of a least-squares line. It fits simple and multiple regressions, returns coefficients in the reversed order documented by Microsoft, and, with stats set to TRUE, exposes the standard errors, the coefficient of determination, the F statistic, and the sums of squares needed to judge the fit. Its power comes with a few obligations: keep the y and x ranges the same size, make sure the x-values vary and the columns are not collinear, and remember that the first coefficient belongs to the last x column. Used with that care, LINEST is the most flexible regression tool built into Excel, and it pairs naturally with TREND, SLOPE, and INTERCEPT when you need only part of the answer.

Author

My name is Jan Bodnar, and I am a passionate programmer with extensive programming experience. I have been writing programming articles since 2007. To date, I have authored over 1,400 articles and 8 e-books. I possess more than ten years of experience in teaching programming.

List all Excel Formulas.