LINEST(Y, X, C , stats)
where,
- is a set of Y values,
- is an optional set of X values,
- 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() 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 -
- For multiple ranges of X-values,
- is a logical value that decides whether to make constant 'b' equal to 0.
- If = TRUE or omitted, 'b' is calculated normally. If = FALSE, 'b' is made equal to 0.
- is a logical value that decides whether to display additional regression statistics.
- If = TRUE, calci returns additional regresstion statistics. If = 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 -
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 cell range A2 to A6 and X co-ordinates in the cell range 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 cell range A2 to A6 and X co-ordinates in the cell 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 cell range A2 to A6 and X co-ordinates in the cell range B2 to B6.
| 0.40909090909090906 | 0 |
| 0.14373989364401724 | |
| 0.6694214876033057 | 2.1320071635561044 |
| 8.099999999999998 | 4 |
| 36.81818181818181 | 18.181818181818183 |