Manuals/calci/LINEST

From ZCubes Wiki
Jump to navigation Jump to search
LINEST (YRange,XRanges,Constant,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(), 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 -


  • For multiple ranges of X-values,

  • Argument values and 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.
  • 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 regression 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 -


  • The additional regression is displayed in the following format where each statistic value is described as below-
---
---
  • is an array of constant multipliers for straight line equation
  • is the constant value of Y when X=0
  • is the standard error value for m1
  • is the standard error value for constant b
  • is the coefficient of determination
  • is the standard error value for Y estimate
  • is the observed F value
  • is the number of degrees of freedom
  • is the regression sum of squares
  • 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:
0.40909090909090906 0
0.14373989364401724
0.6694214876033057 2.1320071635561044
8.099999999999998 4
36.81818181818181 18.181818181818183

Related Videos

LINEST

See Also

References