LINEST ( known-ys [ , [ known-xs ] [ , [ const-flag ] [ , stats-flag ] ] )
Description: Calculates the statistics for a line by using the "least squares" method to calculate a straight line that best fits the data, and returns an array that describes the line.
Mathematical Formula:
The equation for the line is:
y = mx + b
or
y = m1x1 + m2x2 + ... + b (if there are multiple ranges of x-values)
where the dependent y-value is a function of the independent x-values. The m-values are coefficients corresponding to each x-value, and b is a constant value. y, x, and m can be vectors.
When there is only one independent x-variable, the slope and y-intercept values can be obtained directly by using the following formulas:
Slope: INDEX(LINEST(known-ys,known-xs),1)
Y-intercept: INDEX(LINEST(known-ys,known-xs),2)
The accuracy of the line calculated by LINEST depends on the degree of scatter in the data. The more linear the data, the more accurate the LINEST model. LINEST uses the method of least squares for determining the best fit for the data. When there is only one independent x-variable, the calculations for m and b are based on the following formulas:
where x and y are sample means, i.e., x = AVERAGE(known-xs) and y = AVERAGE(known-ys).
Arguments:
|
Name |
Type |
Description |
|
known-ys |
array |
The set of y-values already known in the relationship y=mx+b. If the array known-ys is a single column, then each column of known-xs is interpreted as a separate variable. If the array known-ys is a single row, then each row of known-xs is interpreted as a separate variable. |
|
known-xs |
array |
An optional set of x-values that might already be known in the relationship y=mx+b. The array known-xs can include one or more sets of variables. If only one variable is used, known-ys and known-xs can be ranges of any shape, as long as they have equal dimensions. If more than one variable is used, known-ys shall be a vector (that is, a range with a height of one row or a width of one column). If known-xs is omitted, it is assumed to be the array {1,2,3,...} that is the same size as known-ys. |
|
const-flag |
logical |
Specifies whether to force the constant b to be zero. If TRUE or omitted, b is calculated normally. If FALSE, b is set to zero, and the m-values are adjusted to fit y=mx. |
|
stats-flag |
logical |
Specifies whether to return additional regression statistics. If TRUE, LINEST returns the additional regression statistics (see table below), so the returned array is {mn, mn-1, ..., m1, b; sen, sen-1, ..., se1, seb; r2, sey; F, df; ssreg, ssresid}. If FALSE or omitted, LINEST returns only the m-coefficients and the constant b. |
The additional regression statistics are as follows:
|
Statistic |
Description |
|
se1, se2, ..., sen |
The standard error values for the coefficients m1, m2, ..., mn. |
|
seb |
The standard error value for the constant b. |
|
r2 |
The coefficient of determination. |
|
sey |
The standard error for the y estimate. |
|
F |
The F statistic, or the F-observed value. |
|
df |
The degrees of freedom. |
|
ssreg |
The regression sum of squares. |
|
ssresid |
The residual sum of squares. |
Return Type and Value: array – The array that describes the line, in the form {mn, mn-1, ..., m1, b}. The following illustration shows the order in which the additional regression statistics are returned.
[Example:
LINEST({1,9,5,7},{0,4,2,3},,FALSE) results in a slope of 2 and a y-intercept of 1
end example]