Excel Linest Function

Related Function:

Equation for Line of Best Fit

The Excel Linest Function uses the least squares method to calculate the line of best fit through a supplied set of y- and x- values.

If there is a single range of x-values, the calculated line satisfies the simple straight line equation:

y = mx + b

where,

x is the independent variable;
y is the dependent variable;
m is the slope (gradient) of the line;
b is a constant, equal to the value of y when x = 0.

If there are multiple ranges of x-values, the line of best fit satisfies the following equation:

y = m₁x₁ + m₂x₂ + ... + b

where,

the x's are the independent variable ranges;
y is the dependent variable;
the m's are constant multipliers for each x range;
b is a constant.

Function Description

The Excel LINEST function returns statistical information on the line of best fit, through a supplied set of x- and y- values.

The basic statistical information returned is the array of constants, m_n, m_n-1, ... , b for the equation:

y = m₁x₁ + m₂x₂ + ... + b

or, for a single range of x values, the function returns the constants m and b for the straight line equation:

y = mx + b.

The user can also request that additional regression statistics be returned from the function.

The syntax of the Linest function is:

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

Where the function arguments are:

known_y's

An array of known y-values.

[known_x's]

An optional argument, providing an array of one or more sets of known x-values.

If provided the [known_x's] array should have the same length as the known_y's array;

If omitted, the [known_x's] array takes on the default value {1, 2, 3, ...}.

[const]

An optional logical argument that determines how the constant 'b' is treated in the equation y = m₁x₁ + m₂x₂ + ... + b.

This argument can have the value TRUE or FALSE, meaning:

TRUE (or omitted)	-	the constant b is treated normally;
FALSE	-	the constant b is set to have the value 0.

[stats]

An optional logical argument which specifies whether or not you want the function to return additional regression statistics on the line of best fit.

This argument can have the value TRUE or FALSE, meaning:

FALSE (or omitted)	-	Do NOT return additional regression statistics. I.e. just return the array of values m_n, m_n-1, ... , b.
TRUE	-	DO return additional regression statistics. I.e. Return the array of values m_n, m_n-1, ... , b and also return the additional regression statistics listed in the table below.

The array of statistics returned by the Excel Linest function has the following form:

m_n	m_n-1	...	m₁	b
se_n	se_n-1	...	se₁	se_b
r₂	se_y
F	d_f
ss_reg	ss_resid

where the statistics returned are:

m_i	-	The array of constant multipliers for the straight line equation
b	-	The constant value of y when x=0
se_i	-	The standard error values for the coefficients, m_i
se_b	-	The standard error value for the constant b (returns the #N/A error if the [const] argument is FALSE)
r₂	-	The coefficient of determination
se_y	-	The standard error for the y estimate
F	-	The F statistic, or the F-observed value
d_f	-	The number of degrees of freedom
ss_reg	-	The regression sum of squares
ss_resid	-	The residual sum of squares

Inputting an Array Formula

To input an array formula, you need to first highlight the range of cells for the function result. Type your function into the first cell of the range, and press CTRL-SHIFT-Enter.

Go to the Excel array formulas page for more details.

As the Linest function returns an array of values, it must be entered as an array formula. If the function is not entered as an array formula, only the first 'm' value in the calculated array of statistical information will be displayed in your spreadsheet.

You can see if a function has been input as an array formula, as curly brackets will be inserted around the formula, as it is viewed in the formula bar. This can be seen in the examples below.

Linest Function Example 1

Cells A2-A10 and B2-B10 of the spreadsheet below list a number of known x- and known y-values, and also shows these points, plotted on a chart. Cells D1-E5 of the spreadsheet show the results of the Excel Linest function, which has been used to return statistical information relating to the line of best fit through these points.

As shown in the formula bar, the formula for the Linest function is:

=LINEST( B2:B10, A2:A10, TRUE, TRUE )

The curly brackets around this function show that it has been entered as an array formula.

Example of use of the Excel Linest Function

Cells D1 and E1 give the values of the slope, m as 1.88452381, and the y-intercept, b as 2.419642857. Therefore, the equation for the line of best fit through the given points is:

y = 1.88452381 x + 2.419642857

The remaining cells in the range D2-E5 give the following additional statistics for this curve:

The standard error value for the base m is 0.185347077
The standard error value for the constant b is 0.925189538
The coefficient of determination is 0.936582064
The standard error for the y estimate is 1.201186347
The F statistic is 103.3788683
The number of degrees of freedom is 7
The regression sum of squares is 149.1600595
The residual sum of squares is 10.09994048

Linest Function Example 2

Cells A2-A11, B2-B11 and C2-C11 of the spreadsheet below contain three different sets of independent variables (known x values), and cells D2-D11 of the spreadsheet contain the associated known y-values. Cells F1-H3 of the spreadsheet show the results of the Excel Linest function, which has been used to return statistical information relating to the line of best fit through these points.

As shown in the formula bar, the formula for the Linest function in this case is:

=LINEST( D2:D11, A2:C11, TRUE, TRUE )

Once again, the curly brackets around the function show that it has been entered as an array formula.

Example of use of the Excel Linest Function with multiple independant variables

Cells F1-I1 give the values of the coefficents, m₃, m₂ and m₁ as 3.874869212, 2.027512015 and 4.049870099, respectively and the y-intercept, b as 5.710012148. Therefore, the equation for the line of best fit through the given points is:

y = 4.049870099 x₁ + 2.027512015 x₂ + 3.874869212 x₃ + 5.710012148

The remaining cells in the range F1-I5 give the following additional statistics for this curve:

The standard error values for the coefficients m₃, m₂ and m₁ are 2.135679853, 0.34376891 and 1.266886166, respectively
The standard error value for the constant b is 7.317717472
The coefficient of determination is 0.998527084
The standard error for the y estimate is 1.534343593
The F statistic is 1355.850961
The number of degrees of freedom is 6
The regression sum of squares is 9575.874738
The residual sum of squares is 14.12526158

and the unused cells display the #N/A error.

For further information and examples of the Excel Linest function, see the Microsoft Office website.

Linest Function Errors

If you get an error from the Excel Linest function this is likely to be one of the following:

Common Errors

#REF!

Occurs if the array of [known_x's] is not the same length as the array of known_y's.

#VALUE!

Occurs if either:

Any of the values in the supplied [known_x's] or known_y's arrays are non-numeric (this may include text representations of numbers, as the Linest function does not recognise these as numeric values)

Either the [const] or [stats] argument can not be evaluated to TRUE or FALSE.

Return to the Excel Statistical Functions Page
Return to the List of All Built-In Excel Functions