Difference between revisions of "Documentation/How Tos/Calc: LINEST function"
From Apache OpenOffice Wiki
< Documentation | How Tos
(→See also:) |
OOoWikiBot (Talk | contribs) m (Robot: Automated text replacement %s) |
||
Line 14: | Line 14: | ||
: if <tt>'''allow_const'''</tt> is <tt>'''FALSE'''</tt> the straight line found is forced to pass through the origin (the constant ''a'' is zero; ''y'' = ''bx''). If omitted, <tt>'''allow_const'''</tt> defaults to <tt>'''TRUE'''</tt> (the line is not forced through the origin). | : if <tt>'''allow_const'''</tt> is <tt>'''FALSE'''</tt> the straight line found is forced to pass through the origin (the constant ''a'' is zero; ''y'' = ''bx''). If omitted, <tt>'''allow_const'''</tt> defaults to <tt>'''TRUE'''</tt> (the line is not forced through the origin). | ||
− | : <tt>'''LINEST'''</tt> returns a table (array) of statistics as below and must be entered as an [[Documentation/How_Tos/Using Arrays|array formula]] (for example by using '''Cntrl-Shift-Enter''' rather than just '''Enter''') | + | : <tt>'''LINEST'''</tt> returns a table (array) of statistics as below and must be entered as an * [[Documentation/How_Tos/Using Arrays|array formula]] (for example by using '''Cntrl-Shift-Enter''' rather than just '''Enter''') |
: If <tt>'''stats'''</tt> is omitted or <tt>'''FALSE'''</tt> only the top line of the statistics table is returned. If <tt>'''TRUE'''</tt> the entire table is returned. | : If <tt>'''stats'''</tt> is omitted or <tt>'''FALSE'''</tt> only the top line of the statistics table is returned. If <tt>'''TRUE'''</tt> the entire table is returned. | ||
Line 24: | Line 24: | ||
: ''σ<sub>1</sub>'' to ''σ<sub>n</sub>'' are the standard error values for the line gradients; ''σ<sub>a</sub>'' is the standard error value for the ''y''-axis intercept. | : ''σ<sub>1</sub>'' to ''σ<sub>n</sub>'' are the standard error values for the line gradients; ''σ<sub>a</sub>'' is the standard error value for the ''y''-axis intercept. | ||
− | :''r<sup>2</sup>'' is the determination coefficient ([[Documentation/How_Tos/Calc: RSQ function|RSQ]]); ''σ<sub>y</sub>'' is the standard error value for the ''y'' estimate. | + | :''r<sup>2</sup>'' is the determination coefficient (* [[Documentation/How_Tos/Calc: RSQ function|RSQ]]); ''σ<sub>y</sub>'' is the standard error value for the ''y'' estimate. |
: ''F'' is the F statistic (F-observed value); ''df'' is the number of degrees of freedom. | : ''F'' is the F statistic (F-observed value); ''df'' is the number of degrees of freedom. | ||
Line 45: | Line 45: | ||
: In the example above, you measure the floor area and count the windows of a sample of houses in the area, and make a table with the corresponding sale value (cells <tt>'''A2:C8'''</tt>). To predict the value of other houses in the area you might use: ''value'' = ''a'' + ''b<sub>1</sub>''*''floor_area'' + ''b<sub>2</sub>''*''num_windows'', where ''a'', ''b<sub>1</sub>'' and ''b<sub>2</sub>'' are constants. <tt>'''LINEST(A2:A8;B2:C8;1;1)'''</tt> returns appropriate statistics for that equation. | : In the example above, you measure the floor area and count the windows of a sample of houses in the area, and make a table with the corresponding sale value (cells <tt>'''A2:C8'''</tt>). To predict the value of other houses in the area you might use: ''value'' = ''a'' + ''b<sub>1</sub>''*''floor_area'' + ''b<sub>2</sub>''*''num_windows'', where ''a'', ''b<sub>1</sub>'' and ''b<sub>2</sub>'' are constants. <tt>'''LINEST(A2:A8;B2:C8;1;1)'''</tt> returns appropriate statistics for that equation. | ||
− | + | {{Documentation/SeeAlso| | |
− | [[Documentation/How_Tos/Calc: LOGEST function| | + | * [[Documentation/How_Tos/Calc: LOGEST function|LOGEST]], |
− | [[Documentation/How_Tos/Calc: TREND function| | + | * [[Documentation/How_Tos/Calc: TREND function|TREND]], |
− | [[Documentation/How_Tos/Calc: RSQ function| | + | * [[Documentation/How_Tos/Calc: RSQ function|RSQ]], |
− | [[Documentation/How_Tos/Calc: INTERCEPT function| | + | * [[Documentation/How_Tos/Calc: INTERCEPT function|INTERCEPT]], |
− | [[Documentation/How_Tos/Calc: SLOPE function| | + | * [[Documentation/How_Tos/Calc: SLOPE function|SLOPE]] |
− | [[Documentation/How_Tos/Using Arrays| | + | * [[Documentation/How_Tos/Using Arrays|How To Use Arrays in Calc]] |
− | [[Documentation/How_Tos/Calc: Array functions| | + | * [[Documentation/How_Tos/Calc: Array functions|Array functions]] |
− | [[Documentation/How_Tos/Calc: Functions listed alphabetically| | + | * [[Documentation/How_Tos/Calc: Functions listed alphabetically|Functions listed alphabetically]] |
− | [[Documentation/How_Tos/Calc: Functions listed by category| | + | * [[Documentation/How_Tos/Calc: Functions listed by category|Functions listed by category]]}} |
=== Issues: === | === Issues: === | ||
* You need a good understanding of the statistics involved. | * You need a good understanding of the statistics involved. | ||
* Empty cells in the output array show <tt>'''#N/A'''</tt> (in Calc and Excel). | * Empty cells in the output array show <tt>'''#N/A'''</tt> (in Calc and Excel). |
Revision as of 14:01, 25 February 2009
LINEST
Returns a table of statistics for a straight line that best fits a data set.
Syntax:
LINEST(yvalues; xvalues; allow_const; stats)
- yvalues is a single row or column range specifying the y coordinates in a set of data points.
- xvalues is a corresponding single row or column range specifying the x coordinates. If xvalues is omitted it defaults to 1, 2, 3, ..., n. If there is more than one set of variables xvalues may be a range with corresponding multiple rows or columns.
- LINEST finds a straight line y = a + bx that best fits the data, using linear regression (the "least squares" method). With more than one set of variables the straight line is of the form y = a + b1x1 + b2x2 ... + bnxn.
- if allow_const is FALSE the straight line found is forced to pass through the origin (the constant a is zero; y = bx). If omitted, allow_const defaults to TRUE (the line is not forced through the origin).
- LINEST returns a table (array) of statistics as below and must be entered as an * array formula (for example by using Cntrl-Shift-Enter rather than just Enter)
- If stats is omitted or FALSE only the top line of the statistics table is returned. If TRUE the entire table is returned.
- b1 to bn are the line gradients; a is the y-axis intercept.
- σ1 to σn are the standard error values for the line gradients; σa is the standard error value for the y-axis intercept.
- r2 is the determination coefficient (* RSQ); σy is the standard error value for the y estimate.
- F is the F statistic (F-observed value); df is the number of degrees of freedom.
- ssreg is the regression sum of squares; ssresid is the residual sum of squares.
Example:
- In the example above, cells A2:B8 contain the x,y values for a set of points. LINEST(B2:B8;A2:A8;1;1) returns the statistics for the best fit line through those points.
- In the example above, you measure the floor area and count the windows of a sample of houses in the area, and make a table with the corresponding sale value (cells A2:C8). To predict the value of other houses in the area you might use: value = a + b1*floor_area + b2*num_windows, where a, b1 and b2 are constants. LINEST(A2:A8;B2:C8;1;1) returns appropriate statistics for that equation.
Template:Documentation/SeeAlso
Issues:
- You need a good understanding of the statistics involved.
- Empty cells in the output array show #N/A (in Calc and Excel).