Excel - Microsoft Excel LINEST 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, mn, mn-1, ... , b (or m and b) for the straight line equation. However, you can also request that additional regression statistics be returned. 

The format of the Linest function is : 

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

Where the function arguments are listed in the table below: 

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

where the statistics returned are: 

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 is returned. 

You can see if a function has been input as an array formula, 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 Excel Linest function, used to return statistical information relating to the line of best fit through these points. 

The format of the Linest function is seen in the formula bar. The curly brackets around this function show that it has been entered as an array formula. 

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 D1 - E5 give the following additional statistics for this curve: 

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 Excel Linest function, used to return statistical information relating to the line of best fit through these points. 

Again, the format of the Linest function is seen in the formula bar and it can be seen, (from the curly brackets), that the function has been entered as an array formula. 

Cells F1 - I1 give the values of the coefficents, m3, m2 and m1 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 x1 + 2.027512015 x2 + 3.874869212 x3 + 5.710012148 

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

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

Linest Function Errors

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

Common Errors