Manuals/calci/LINEST

LINEST(Y, X, C , stats)

where,

• $Y$ is a set of Y values,
• $X$ is an optional set of X values,
• $C$ is a logical value TRUE or FALSE, that decides whether to force the constant 'b' to 0,
• $stats$ is a logical value TRUE or FALSE, that decides whether to return additional regression statistics.

LINEST() is an array function that calculates the statistics for a line by using the 'least squares' method to calculate a straight line that closely fits the input data.

Description

• If 'Y' is the point on y-axis, 'X' is the point on x-axis, 'm' is a constant indicating slope of the line and 'b' is the constant value at which the line crosses y-axis (Y intercept),

then equation of line is -

$Y=mX+b$

• For multiple ranges of X-values,

$Y=m1X1+m2X2+......+b$

• $C$ is a logical value that decides whether to make constant 'b' equal to 0.
• If $C$ = TRUE or omitted, 'b' is calculated normally. If $C$ = FALSE, 'b' is made equal to 0.
• $stats$ is a logical value that decides whether to display additional regression statistics.
• If $stats$ = TRUE, calci returns additional regresstion statistics. If $stats$ = FALSE or omitted, Calci returns the values of 'm'(slope) and the constant 'b'.
• When there is only one independent X variable, Slope(m) and Y intercept (b) can be calculated using following formulas -

$Slope(m)=INDEX(LINEST(Y,X),1)$

$Yintercept(b)=INDEX(LINEST(Y,X),2)$

• The additional regression is displayed in the following format where each statistic value is described as below-

$m_{n}$	$m_{n-1}$	---	$m_{1}$	$b$
$se_{n}$	$se_{n-1}$	---	$se_{1}$	$se_{b}$
$r_{2}$	$se_{y}$
$F$	$d_{f}$
$ss_{reg}$	$ss_{resld}$

• $m_{n}$ is an array of constant multipliers for straight line equation
• $b$ is the constant value of Y when X=0
• $se_{1}$ is the standard error value for m1
• $se_{b}$ is the standard error value for constant b
• $r_{2}$ is the coefficient of determination
• $se_{y}$ is the standard error value for Y estimate
• $F$ is the observed F value
• $d_{f}$ is the number of degrees of freedom
• $ss_{reg}$ is the regression sum of squares
• $ss_{resld}$ is the residual sum of squares

Examples

Y co-ordinates	X co-ordinates
1	2
5	4
4	6
3	8
2	10

=LINEST(A2:A6,B2:B6,TRUE,FALSE) : Calculates the statistics of line with Y co-ordinates in cells A2 to A6 and X co-ordinates in cells B2 to B6.
Returns m=0 and b=3 as a result.
=LINEST(A2:A6,B2:B6,FALSE,FALSE): Calculates the statistics of line with Y co-ordinates in cells A2 to A6 and X co-ordinates in cells range B2 to B6.
Returns m=0.4090909090909091 and b=0 as a result.

=LINEST(A2:A6,B2:B6,FALSE,TRUE) : Displays the additional regression statistics of line with Y co-ordinates in cells A2 to A6 and 
X co-ordinates in cells B2 to B6 as shown below -

0.40909090909090906	0
0.14373989364401724
0.6694214876033057	2.1320071635561044
8.099999999999998	4
36.81818181818181	18.181818181818183

See Also

• LOGEST

References

• Linear Equation