Syntax:
LINEST(y_values [, x_values] [, Constant] [, Stats])
Description:
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 trendlines is:
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, the constant b is the y-intercept point (the point where the trendline intersects the y axis).
If more than one range of x-values is given, the equation is:
y = (m1*x1) + (m2*x2) + ... + (mn*xn) + b
In this case, LINEST returns an array in the form {mn, mn-1, ... , m2, m1, b}.
Additionally, if the optional argument Stats is used, several statistics of the regression (standard error, coefficient of determination, etc.) are appended 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 can optionally specify the x-coordinates, that is, the independent values. Also x_values can be either a cell reference or an array. If x_values are omitted, the values 1, 2, 3, ... will be used automatically. (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 calculated automatically or forced to equal zero:
TRUE or omitted: b will be calculated from the given data.
FALSE: b is forced to equal 0 (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 regression statistics. In this case, a matrix in the following form will be returned:
{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 like this:
The abbreviations used in this illustration 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.
Annotation:
The linear regression is performed with this function using the least squares method.
Example:
LINEST({4; 5; 6}) provides 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 LINES function automatically uses the values 1, 2, 3, ...)
See also: