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 xvalues, the calculated line satisfies the simple straight line equation:
where,
If there are multiple ranges of xvalues, the line of best fit satisfies the following equation:
where,
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_{n1}, ... , b for the equation:
or, for a single range of x values, the function returns the constants m and b for the straight line equation:
The user can also request that additional regression statistics be returned from the function.
The syntax of the Linest function is:
Where the function arguments are:
known_y's    An array of known yvalues.  
[known_x's]    An optional argument, providing an array of one or more sets of known xvalues. 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_{1}x_{1} + m_{2}x_{2} + ... + b. This argument can have the value TRUE or FALSE, meaning:  
 
[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:  

The array of statistics returned by the Excel Linest function has the following form:
m_{n}  m_{n1}  ...  m_{1}  b 
se_{n}  se_{n1}  ...  se_{1}  se_{b} 
r_{2}  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_{2}    The coefficient of determination 
se_{y}    The standard error for the y estimate 
F    The F statistic, or the Fobserved value 
d_{f}    The number of degrees of freedom 
ss_{reg}    The regression sum of squares 
ss_{resid}    The residual sum of squares 
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 CTRLSHIFTEnter.
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.
Cells A2A10 and B2B10 of the spreadsheet below list a number of known x and known yvalues, and also shows these points, plotted on a chart. Cells D1E5 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:
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 yintercept, b as 2.419642857. Therefore, the equation for the line of best fit through the given points is:
The remaining cells in the range D2E5 give the following additional statistics for this curve:
Cells A2A11, B2B11 and C2C11 of the spreadsheet below contain three different sets of independent variables (known x values), and cells D2D11 of the spreadsheet contain the associated known yvalues. Cells F1H3 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:
Once again, the curly brackets around the function show that it has been entered as an array formula.
Cells F1I1 give the values of the coefficents, m_{3}, m_{2} and m_{1} as 3.874869212, 2.027512015 and 4.049870099, respectively and the yintercept, b as 5.710012148. Therefore, the equation for the line of best fit through the given points is:
The remaining cells in the range F1I5 give the following additional statistics for this curve:
and the unused cells display the #N/A error.
For further information and examples of the Excel Linest function, see the Microsoft Office website.
If you get an error from the Excel Linest function this is likely to be one of the following:
#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:
