Documentation/Calc Functions/LOGEST

Function name:
LOGEST

Category:
Array

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

Syntax:
LOGEST(Data Y [; Data X [; Function Type [; Stats]]])

Returns:
Returns an array of decimal numbers that are the statistics for an exponential curve 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 curve. 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 curve. 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.

Function Type is a Boolean expression, or a reference to a cell containing a Boolean expression, that determines whether the regression curve (which has the general form $$y(x)=a~\times~b^x$$) has a constant a that is forced to the value 1. If Function Type is FALSE or zero, the regression curve is forced to use the value 1 for a. If Function Type is TRUE, non-zero, or omitted, the regression curve uses the best-fit value for a.

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 LOGEST 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 LOGEST 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 LOGEST 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 LOGEST reports an invalid argument error (Err:502).

Details specific to LOGEST function

 * LOGEST finds the exponential curve $$y(x)=a~\times~b^x$$ that best fits the data. With more than one set of variables the curve is of the form: $$y=a~\times~b_{1}^{~x_{1}}~\times~b_{2}^{~x_{2}}~\times~...~\times~b_{n}^{~x_{n}}$$.


 * In order to fit the curve, LOGEST uses linear regression based on the following equation (formed by taking the natural logarithm of the previous equation): $$ln(y) = ln(a)~+~x_{1} \times ln(b_{1})~+~x_{2} \times ln(b_{2}) ~+~ ... ~+~ x_{n} \times ln(b_{n})$$.


 * 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.
 * $${b}_{1}$$ to $${b}_{n}$$ and $$a$$ are the coefficients for the regression equation.
 * $${\sigma }_{1}$$ to $${\sigma }_{n}$$ are the standard error values for the $$ln(b)$$ values.
 * $${\sigma }_{a}$$ is the standard error value for the $$ln(a)$$ value.
 * $${r}^{2}$$ is the determination coefficient.
 * $${\sigma }_{y}$$ is the standard error value for the $$ln(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 LOGEST includes two x-variables. The cells containing the output values from LOGEST are limited to display only four decimal places (to reduce visual clutter).

Related LibreOffice functions:
LINEST

ODF standard:
Section 6.18.42, part 2

Equivalent Excel functions:
LOGEST