Syntax:
LINEST(y_values [, x_values] [, Constant] [, Stats])
Description:
This returns statistics of a linear regression.
Linear regression is a statistical technique that adapts a line (called "trendline" or "best-fit line") to a set of data points (for example, the results of a series of measurements).
The equation for this trendline is as follows:
y = m*x + b
The LINEST function calculates a trendline from the given y values and x values, and then returns an array in the form {m,b}. The coefficient m is the slope of the trendline, and the constant b is the y-intercept point (the point where the trendline intersects the y axis).
If there are several ranges with x values, the equation is as follows:
y = (m1*x1) + (m2*x2) + ... + (mn*xn) + b
In this case, LINEST returns an array in the form {mn , mn-1 , ... , m2 , m1 , b}.
If the optional argument Stats is used, several statistics of the regression (standard error, coefficient of determination, etc.) are added to the resulting array.
The LINEST function expects the following arguments:
y_values: Here, you specify the existing y coordinates, that is, the dependent values. y_values can be either a cell reference or an array.
x_values: Here, you have the option of specifying the x coordinates, that is, the independent values. x_values can also be either a cell reference or an array. If x_values are omitted, the function automatically uses the values 1, 2, 3, ... (according to the number of existing y_values).
Constant (optional) is a logical value that lets you specify if the constant b (the y-intercept point) should be set to zero:
TRUE or omitted: b is automatically calculated from the available data.
FALSE: b is set to zero. This forces the trendline to run through the zero point. The m coefficient(s) are adjusted accordingly.
Stats (optional) is a logical value that lets you specify if additional regression statistics should be returned:
FALSE or omitted: Do not return additional statistics.
TRUE: Return additional statistics. In this case, the function returns an array that not only includes m and b, but also the following values:
{mn , mn-1 , ... , m2 , m1 , b ; smn , smn-1 , ... , sm2 , sm1 , sb ; R2 , sey ; F. df ; ssreg , ssresid}
A graphical representation of this array would look as follows:
The above abbreviations represent the following values:
Abbreviation |
Explanation |
sm1, sm2, etc. |
Standard error for the coefficients m1, m2, etc. |
sb |
Standard error for constant b |
R2 |
Square of the Pearson product moment correlation coefficient |
sey |
Standard error for y |
F |
F statistic |
df |
Degrees of freedom |
ssreg |
Regression sum of squares |
ssresid |
Residual sum of squares |
Note:
Formulas using this function have to be entered as an array formula. For more information, see Working with arrays.
Additional info:
The linear regression is performed with this function using the least squares method.
Example:
LINEST({4; 5; 6}) returns the matrix {1.3}. This indicates that the coefficient m equals 1 and the constant b equals 3. (Since the x values were not specified, the LINEST function automatically uses the values 1, 2, 3, ...)
See also: