Manuals/calci/LINEST

LINEST (YRange,XRanges,Constant,Stats)

where,

$YRange$ is a set of Y values,
$XRanges$ is an optional set of X values,
$Constant$ is a logical value TRUE or FALSE, that decides whether to force the constant 'b' to 0,
is a logical value TRUE or FALSE, that decides whether to return additional regression statistics.
- LINEST(), returns the parameters of a linear trend.

Description

This function 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.

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$

Argument values $XRanges$ and $YRange$ should be numeric, else Calci displays NaN error message.
The length of array of X values should be equal to length of array of Y values, else Calci displays #NULL error message.
$Constant$ is a logical value that decides whether to make constant 'b' equal to 0.
If $Constant$ = TRUE or omitted, 'b' is calculated normally. If $Constant$ = 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 regression 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: