Returns an array that describes the best fit linear trend using least squares method.
LINEST(known_data_y, [known_data_x], [constant], [statistics])
- known_data_y - The array or range containing dependent (y) values.
- known_data_x - [OPTIONAL] The array or range containing independent (x) values corresponding with known_data_y.
- constant - [OPTIONAL] A logical value that determines whether to calculate the constant ‘b’ or to set it to 0.
- If set to TRUE or omitted, b is calculated normally.
- If set to FALSE, b is set equal to 0 (zero) and the m-values are adjusted so that y = mx.
- statistics - [OPTIONAL] A logical value that determines whether to return additional regression statistics. FALSE by default. The regression coefficient and y-intercept are always returned, additional optional regression statistics include:
- The standard error for each coefficient and the intercept.
- The coefficient of determination.
- Standard error for the dependent variable values.
- The F-statistic, or F-observed value indicating whether the observed relationship between dependent and independent variables is random rather than linear.
- The degrees of freedom, useful in looking up F statistic values in a reference table to estimate a confidence level.
- The regression sum of squares.
- The residual sum of squares.
LINEST(B2:B10,A2:A10) returns the regression coefficient and y-intercept from the inputed y and x data.
LINEST(B2:B10,A2:A10,FALSE,TRUE) sets b to zero and returns the regression coefficient and y-intercept from the inputed y and x data, as well as the additional regression statistics.