Difference between revisions of "Documentation/How Tos/Calc: LINEST function"
From Apache OpenOffice Wiki
< Documentation | How Tos
(Initial content) |
m (→Syntax:) |
||
Line 8: | Line 8: | ||
: <tt>'''yvalues'''</tt> is a single row or column range specifying the <i>y</i> coordinates in a set of data points. | : <tt>'''yvalues'''</tt> is a single row or column range specifying the <i>y</i> coordinates in a set of data points. | ||
− | : <tt>'''xvalues'''</tt> is a corresponding single row or column range specifying the <i>x</i> coordinates. If <tt>'''xvalues'''</tt> is omitted it defaults to <tt>'''1, 2, 3, ..., n'''</tt>. If there is more than one set of variables <tt>'''xvalues'''</tt> may be a range with corresponding | + | : <tt>'''xvalues'''</tt> is a corresponding single row or column range specifying the <i>x</i> coordinates. If <tt>'''xvalues'''</tt> is omitted it defaults to <tt>'''1, 2, 3, ..., n'''</tt>. If there is more than one set of variables <tt>'''xvalues'''</tt> may be a range with corresponding multiple rows or columns. |
: <tt>'''LINEST'''</tt> 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'' + ''b<sub>1</sub>x<sub>1</sub>'' + ''b<sub>2</sub>x<sub>2</sub>'' ... + ''b<sub>n</sub>x<sub>n</sub>''. | : <tt>'''LINEST'''</tt> 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'' + ''b<sub>1</sub>x<sub>1</sub>'' + ''b<sub>2</sub>x<sub>2</sub>'' ... + ''b<sub>n</sub>x<sub>n</sub>''. |
Revision as of 07:03, 26 March 2008
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 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:
to follow