LINEST

Returns an array that describes the best fit linear trend using least squares method.

Sample Usage

LINEST(B2:B10,A2:A10)
LINEST(B2:B10,A2:A10,FALSE,TRUE)

Syntax

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.

Examples

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.