Difference between revisions of "Manuals/calci/LINEST"

From ZCubes Wiki
Jump to navigation Jump to search
(Created page with "<div id="6SpaceContent" class="zcontent" align="left"> '''LINEST'''('''Y''',X,C,stats)'''Where Y '''is the set of y-values and X  is an optional set of x-values that in th...")
 
Line 1: Line 1:
−
<div id="6SpaceContent" class="zcontent" align="left">
+
=LINEST(Y, X, C , stats)=
  
−
'''LINEST'''('''Y''',X,C,stats)'''Where Y '''is the set of y-values and X  is an optional set of x-values that in the relationship y = mx + b.
+
where,
 +
*<math>Y</math> is a set of Y  values,
 +
*<math>X</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>stats</math> is a logical value TRUE or FALSE, that decides whether to return additional regression statistics.
  
−
'''C '''  is a logical value specifying whether to force the constant b to equal 0 and stats  are a logical value specifying 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.
  
−
</div>
+
== Description ==
−
----
 
−
<div id="1SpaceContent" class="zcontent" align="left">
 
  
−
This function calculates the k-th largest value in a data set.
+
*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 -
  
−
</div>
+
<math>Y=mX + b</math>
−
----
 
−
<div id="7SpaceContent" class="zcontent" align="left">·    <font face="Arial">When </font>there is  only one independent x-variable, then the slope and y-intercept values directly by using the following formulas:
 
  
−
Slope:<br />=INDEX(LINEST(Y,X),1)
+
*For multiple ranges of X-values,
  
−
Y-intercept:<br />=INDEX(LINEST(Y,X) 2)
+
<math>Y = m1X1 + m2X2 +......+ b</math>
  
−
·          The accuracy of the line is calculated by using LINEST.
+
*<math>C</math> is  a logical value that decides whether to make constant 'b' equal to 0.
 +
*If <math>C</math> = TRUE or omitted, 'b' is calculated normally. If <math>C</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 regresstion 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 -
  
−
Formulas:-
+
<math>Slope (m) = INDEX(LINEST(Y, X),1) </math>
  
−
</div>
+
<math>Y intercept (b) = INDEX(LINEST(Y, X),2) </math>
−
----
 
−
<div id="12SpaceContent" class="zcontent" align="left"><div class="ZEditBox" align="left">
 
  
−
LINEST
+
== Examples ==
  
−
</div></div>
+
<div id="2SpaceContent" class="zcontent" align="left">
−
----
 
−
<div id="8SpaceContent" class="zcontent" align="left">
 
  
−
Lets see an example,
+
{| id="TABLE3" class="SpreadSheet blue"
 +
|- class="even"
 +
| class="sshl_f" | '''Y co-ordinates'''
 +
| class="sshl_f" | '''X co-ordinates'''
 +
| class="sshl_f" |
  
−
LINEST(Y,X,C,Stats)
+
|- class="odd"
 +
| class="sshl_f" | 1
 +
| class="sshl_f" | 2
 +
| class="sshl_f" |
  
−
B             C
+
|- class="even"
 +
| class="sshl_f" | 5
 +
| class="sshl_f" | 4
 +
| class="sshl_f" |
  
−
1             0
+
|- class="odd"
 +
| class="sshl_f" | 4
 +
| class="sshl_f" | 6
 +
| class="sshl_f" |
  
−
9             4
+
|- class="even"
 +
| class="sshl_f" | 3
 +
| class="sshl_f" | 8
 +
| class="sshl_f" |
  
−
5             2
+
|- class="odd"
 +
| class="sshl_f" | 2
 +
| class="sshl_f" | 10
 +
| class="sshl_f" |
 +
| class="sshl_f" |
 +
|}
  
−
7             3
+
=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,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,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.
  
−
<nowiki>=LINEST(B2:B5,C2:C5,FALSE)</nowiki>
+
<div id="5SpaceContent" class="zcontent" align="left">
  
−
<nowiki>=LARGE(B2:C6,8) is 5</nowiki>
+
{| class="SpreadSheet blue"
−
 
 
−
</div>
 
−
----
 
−
<div id="10SpaceContent" class="zcontent" align="left"><div class="ZEditBox" align="justify">Syntax </div><div class="ZEditBox"><center></center></div></div>
 
−
----
 
−
<div id="4SpaceContent" class="zcontent" align="left"><div class="ZEditBox" align="justify">Remarks </div></div>
 
−
----
 
−
<div id="3SpaceContent" class="zcontent" align="left"><div class="ZEditBox" align="justify">Examples </div></div>
 
−
----
 
−
<div id="11SpaceContent" class="zcontent" align="left"><div class="ZEditBox" align="justify">Description </div></div>
 
−
----
 
−
<div id="2SpaceContent" class="zcontent" align="left">
 
−
 
 
−
{| id="TABLE3" class="SpreadSheet blue"
 
 
|- class="even"
 
|- class="even"
−
| class="    " |
+
| 0.40909090909090906
−
| Column1
+
| 0
−
| class="  " | Column2
 
−
| class="  " | Column3
 
−
| class="  " | Column4
 
 
|- class="odd"
 
|- class="odd"
−
| class=" " | Row1
+
| 0.14373989364401724
−
| class="sshl_f " | 1
+
|  
−
| class="sshl_f " | 0
 
−
| class="sshl_f" |
 
−
| class="sshl_f" |
 
 
|- class="even"
 
|- class="even"
−
| class="  " | Row2
+
| 0.6694214876033057
−
| class="sshl_f" | 9
+
| 2.1320071635561044
−
| class="sshl_f" | 4
 
−
| class="SelectTD" |
 
−
| class="sshl_f" |
 
 
|- class="odd"
 
|- class="odd"
−
| Row3
+
| 8.099999999999998
−
| class="sshl_f" | 5
+
| 4
−
| class="sshl_f" | 2
 
−
| class="  " |
 
−
| class="sshl_f" |
 
 
|- class="even"
 
|- class="even"
−
| Row4
+
| 36.81818181818181
−
| class=" " | 7
+
| 18.181818181818183
−
| class=" " | 3
+
 
−
| class="sshl_f" |
 
−
| class="sshl_f" |
 
−
|- class="odd"
 
−
| class=" " | Row5
 
−
| class="sshl_f" |
 
−
| class="sshl_f" |
 
−
| class="sshl_f" |
 
−
| class="sshl_f" |
 
−
|- class="even"
 
−
| Row6
 
−
|
 
−
| class="sshl_f  " |
 
−
| class="sshl_f" |
 
−
| class="sshl_f" |
 
 
|}
 
|}
 +
 +
== See Also ==
 +
 +
*[[Manuals/calci/LOGEST| LOGEST]]
  
−
<div align="left">[[Image:calci1.gif]]</div></div>
+
== References ==
−
----
+
*[http://en.wikipedia.org/wiki/Linear_equation Linear Equation]

Revision as of 17:45, 28 January 2014

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

See Also

References

Retrieved from "http://wiki.zcubes.com/index.php?title=Manuals/calci/LINEST&oldid=197496"