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)$

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.

0.40909090909090906	0
0.14373989364401724
0.6694214876033057	2.1320071635561044
8.099999999999998	4
36.81818181818181	18.181818181818183

See Also

• LOGEST

References

• Linear Equation