Difference between revisions of "Manuals/calci/LINEST"

Latest revision as of 16:13, 10 August 2018

LINEST (YRange,XRanges,Constant,Stats)

where,

$YRange$ is a set of Y values,
$XRanges$ is an optional set of X values,
$Constant$ 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 -

 $Y=mX+b$

For multiple ranges of X-values,

 $Y=m1X1+m2X2+......+b$

Argument values $XRanges$ and $YRange$ 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.
$Constant$ is a logical value that decides whether to make constant 'b' equal to 0.
If $Constant$ = TRUE or omitted, 'b' is calculated normally. If $Constant$ = 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 regression 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)$

The additional regression is displayed in the following format where each statistic value is described as below-

$m_{n}$	$m_{n-1}$	---	$m_{1}$	$b$
$se_{n}$	$se_{n-1}$	---	$se_{1}$	$se_{b}$
$r_{2}$	$se_{y}$
$F$	$d_{f}$
$ss_{reg}$	$ss_{resld}$

$m_{n}$ is an array of constant multipliers for straight line equation
$b$ is the constant value of Y when X=0
$se_{1}$ is the standard error value for m1
$se_{b}$ is the standard error value for constant b
$r_{2}$ is the coefficient of determination
$se_{y}$ is the standard error value for Y estimate
$F$ is the observed F value
$d_{f}$ is the number of degrees of freedom
$ss_{reg}$ is the regression sum of squares
$ss_{resld}$ 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

References

Linear Equation

List of Main Z Functions

Z3 home

@@ Line 1: / Line 1: @@
-=LINEST(Y, X, C , stats)=
+<div style="font-size:30px">'''LINEST (YRange,XRanges,Constant,Stats)'''</div><br/>
 where,
-*<math>Y</math> is a set of Y  values,
+*<math>YRange</math> is a set of Y  values,
-*<math>X</math> is an optional set of X  values,
+*<math>XRanges</math> is an optional set of X  values,
-*<math>C</math> is a logical value TRUE or FALSE, that decides whether to force the constant 'b' to 0,
+*<math>Constant</math> is a logical value TRUE or FALSE, that decides whether to force the constant 'b' to 0,
-*<math>stats</math> is a logical value TRUE or FALSE, that decides whether to return additional regression statistics.
+*<math>Stats</math> is a logical value TRUE or FALSE, that decides whether to return additional regression statistics.
+**LINEST(), returns the parameters of a linear trend.
-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 ==
+*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 -
+then equation of line is -
-<math>Y=mX + b</math>
+ <math>Y=mX + b</math>
 *For multiple ranges of X-values,
-<math>Y = m1X1 + m2X2 +......+ b</math>
+ <math>Y = m1X1 + m2X2 +......+ b</math>
-*<math>C</math> is  a logical value that decides whether to make constant 'b' equal to 0.
+*Argument values <math>XRanges</math> and <math>YRange</math> should be numeric, else Calci displays NaN error message.
-*If <math>C</math> = TRUE or omitted, 'b' is calculated normally. If <math>C</math> = FALSE, 'b' is made equal to 0.
+*The length of array of X values should be equal to length of array of Y values, else Calci displays #NULL error message.
-*<math>stats</math> is a logical value that decides whether to display additional regression statistics.
+*<math>Constant</math> is  a logical value that decides whether to make constant 'b' equal to 0.
-*If <math>stats</math> = TRUE, calci returns additional regresstion statistics. If <math>stats</math> = FALSE or omitted, Calci returns the values of 'm'(slope) and the constant 'b'.
+*If <math>Constant</math> = TRUE or omitted, 'b' is calculated normally. If <math>Constant</math> = FALSE, 'b' is made equal to 0.
+*<math>Stats</math> is a logical value that decides whether to display additional regression statistics.
+*If <math>Stats</math> = TRUE, calci returns additional regression statistics. If <math>,Stats</math> = 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 -
-<math>Slope (m) = INDEX(LINEST(Y, X),1) </math>
+ <math>Slope (m) = INDEX(LINEST(Y, X),1) </math>
+ <math>Y intercept (b) = INDEX(LINEST(Y, X),2) </math>
+*The additional regression is displayed in the following format where each statistic value is described as below-
+{| class="wikitable"
+|-
+| <math>m_n</math> || <math>m_{n-1}</math> || --- || <math>m_1</math> || <math>b</math>
+|-
+| <math>se_n</math> || <math>se_{n-1}</math> || --- || <math>se_1</math> || <math>se_b</math>
+|-
+| <math>r_2</math> || <math>se_y</math> ||  || ||
+|-
+| <math>F</math> || <math>d_f</math> ||  ||  ||
+|-
+| <math>ss_{reg}</math> || <math>ss_{resld}</math> || ||  ||
+|}
-<math>Y intercept (b) = INDEX(LINEST(Y, X),2) </math>
+*<math>m_n</math> is an array of constant multipliers for straight line equation
+*<math>b</math> is the constant value of Y when X=0
+*<math>se_1</math> is the standard error value for m1
+*<math>se_b</math> is the standard error value for constant b
+*<math>r_2</math> is the coefficient of determination
+*<math>se_y</math> is the standard error value for Y estimate
+*<math>F</math> is the observed F value
+*<math>d_f</math> is the number of degrees of freedom
+*<math>ss_{reg}</math> is the regression sum of squares
+*<math>ss_{resld}</math> is the residual sum of squares
 == Examples ==
@@ Line 64: / Line 90: @@
 | class="sshl_f" | 10
 | class="sshl_f" |
-| class="sshl_f" |
 |}
-  =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.<br />Returns '''m=0''' and '''b=3''' as a result.
+  =LINEST(A2:A6,B2:B6,TRUE,FALSE) : Calculates the statistics of line with Y co-ordinates in cells <br /> 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 cell range A2 to A6 and X co-ordinates in the cell range B2 to B6.<br />Returns '''m=0.4090909090909091''' and '''b=0''' as a result.
+  =LINEST(A2:A6,B2:B6,FALSE,FALSE): Calculates the statistics of line with Y co-ordinates in cells <br /> 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 cell range A2 to A6 and X co-ordinates in the cell range B2 to B6.
+  =LINEST(A2:A6,B2:B6,FALSE,TRUE) : Displays the additional regression statistics of line <br />with Y co-ordinates in cells A2 to A6 and X co-ordinates in cells B2 to B6 as shown below:
 <div id="5SpaceContent" class="zcontent" align="left">
@@ Line 91: / Line 118: @@
 |}
+==Related Videos==
+{{#ev:youtube|6wbcPbYbq6M|280|center|LINEST}}
 == See Also ==
@@ Line 98: / Line 129: @@
 == References ==
 *[http://en.wikipedia.org/wiki/Linear_equation Linear Equation]
+*[[Z_API_Functions | List of Main Z Functions]]
+*[[ Z3 |   Z3 home ]]

Difference between revisions of "Manuals/calci/LINEST"

Latest revision as of 16:13, 10 August 2018

Contents

Description

Examples

Related Videos

See Also

References

Navigation menu

Search