Documentation/How Tos/Calc: LINEST function

LINEST

Returns a table of statistics for a straight line that best fits a data set.

Syntax:

LINEST(yvalues; xvalues; allow_const; stats)

yvalues is a single row or column range specifying the y coordinates in a set of data points.

xvalues is a corresponding single row or column range specifying the x coordinates. If xvalues is omitted it defaults to 1, 2, 3, ..., n. If there is more than one set of variables xvalues may be a range with corresponding multiple rows or columns.

LINEST finds a straight line y = a + bx that best fits the data, using linear regression (the "least squares" method). With more than one set of variables the straight line is of the form y = a + b₁x₁ + b₂x₂ ... + b_nx_n.

if allow_const is FALSE the straight line found is forced to pass through the origin (the constant a is zero; y = bx). If omitted, allow_const defaults to TRUE (the line is not forced through the origin).

LINEST returns a table (array) of statistics as below and must be entered as an array formula (for example by using Cntrl-Shift-Enter rather than just Enter)

If stats is omitted or FALSE only the top line of the statistics table is returned. If TRUE the entire table is returned.

b₁ to b_n are the line gradients; a is the y-axis intercept.

σ₁ to σ_n are the standard error values for the line gradients; σ_a is the standard error value for the y-axis intercept.

r² is the determination coefficient (RSQ); σ_y standard error value for the y estimate.

F is the F statistic (F-observed value); df is the number of degrees of freedom.

ss_reg is the regression sum of squares; ss_resid is the residual sum of squares.

Example:

to follow

See also:

LOGEST, TREND, RSQ, INTERCEPT, SLOPE

How To Use Arrays in Calc

Array functions