Documentation/Calc Functions/LINEST

Function name:
LINEST

Category:
Array

Summary:
Calculates the straight line that best fits a supplied data set and optionally returns detailed statistics on the regression.

Syntax:
LINEST(Data Y [; Data X [; Linear type [; Stats]]])

Returns:
Returns an array of decimal numbers that are the statistics for a straight line that best fits the input data set. The dimensions of the returned array depend on the nature of the arguments.

Arguments:
Data Y contains the set of known y-values to be used to determine the regression line. This argument can take the form of an explicit cell range (such as A1:C100), the name of a named range, the name of a database range, or an inline constant array.

Data X contains the set of known x-values to be used to determine the regression equation. This argument can take the form of an explicit cell range (such as D1:D100), the name of a named range, the name of a database range, or an inline constant array. If omitted, it is set to the array {1, 2, 3, …, k}, where k is the number of values in Data Y. If there is more than one set of variables, Data X may be a range with corresponding multiple rows or columns.

Linear type is a Boolean expression, or a reference to a cell containing a Boolean expression, that determines whether the regression line is forced to pass through the origin. If Linear type is FALSE or zero, the straight line found is forced to pass through the origin. If Linear type is TRUE, non-zero, or omitted, the line is not forced to pass through the origin.

Stats is a Boolean expression, or a reference to a cell containing a Boolean expression, that determines whether the full statistics table is returned or just the top line. If Stats is FALSE, zero, or omitted, only the top line of the statistics table is returned. If Stats is TRUE or non-zero, the full statistics table is returned.


 * If any entry in Data Y or Data X is non-numeric, then LINEST reports an invalid argument error (Err:502).
 * If Data Y is neither a single-column vector nor a single-row vector, then Data Y and Data X must have the same dimensions. If this condition is not met, then LINEST reports an invalid argument error (Err:502).
 * If Data Y is a single-column vector, then Data Y and Data X must have the same number of rows. If this condition is not met, then LINEST reports an invalid argument error (Err:502).
 * If Data Y is a single-row vector, then Data Y and Data X must have the same number of columns. If this condition is not met, then LINEST reports an invalid argument error (Err:502).

Details specific to LINEST function

 * More background information can be found at Wikipedia's Linear regression page and in the sources that it references.


 * The following table illustrates the array of statistics returned when the Stats argument is set TRUE. The top left cell of the table corresponds to the top-left cell of the cell area in which the array formula was entered. It is assumed that with $$n$$ sets of variables, the straight line is of the form $$y=a~+~b_{1}x_{1}~+~b_{2}x_{2}~+~...~+~b_{n}x_{n}$$.
 * $${b}_{1}$$ to $${b}_{n}$$ are the line gradients or slopes. $$n$$ is the number of sets of variables contained in the Data X argument. These slope values are given in reverse order; that is, the slope for the first set of variables is given last and vice-versa.
 * $$a$$ is the y-axis intercept.
 * $${\sigma }_{1}$$ to $${\sigma }_{n}$$ are the standard error values for the line gradients.
 * $${\sigma }_{a}$$ is the standard error value for the y-axis intercept.
 * $${r}^{2}$$ is the determination coefficient.
 * $${\sigma }_{y}$$ is the standard error value for the y estimate.
 * $$F$$ is the F statistic (F-observed value).
 * $$\mathit{df}$$ is the number of degrees of freedom.
 * $${\mathit{SS}}_{\mathit{reg}}$$ is the regression sum of squares.
 * $${\mathit{SS}}_{\mathit{resid}}$$ is the residual sum of squares.


 * Empty cells in this table contain the #N/A error.


 * When the Stats argument is set FALSE, only the top line is returned (containing $${b}_{1}$$ to $${b}_{n}$$ and $$a$$).

Examples:
The subsequent example uses the following spreadsheet data, in which the data input to LINEST includes two x-variables. The cells containing the output values from LINEST are limited to display only four decimal places (to reduce visual clutter).

Related LibreOffice functions:
LOGEST

ODF standard:
Section 6.18.41, part 2

Equivalent Excel functions:
LINEST