LINEST

Syntax:

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

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: