Difference between revisions of "Documentation/How Tos/Calc: LINEST function"
From Apache OpenOffice Wiki
< Documentation | How Tos
m |
m |
||
Line 60: | Line 60: | ||
* [[Documentation/How_Tos/Calc: Functions listed by category|Functions listed by category]]}} | * [[Documentation/How_Tos/Calc: Functions listed by category|Functions listed by category]]}} | ||
[[Category: Documentation/Reference/Calc/Array functions]] | [[Category: Documentation/Reference/Calc/Array functions]] | ||
+ | [[Category: Documentation/Reference/Calc/Statistical functions]] |
Revision as of 12:42, 17 May 2010
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 + b_{1}x_{1} + b_{2}x_{2} ... + b_{n}x_{n}.
- 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.
- b_{1} to b_{n} 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.
- r^{2} 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.
- ss_{reg} is the regression sum of squares; ss_{resid} 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 + b_{1}*floor_area + b_{2}*num_windows, where a, b_{1} and b_{2} are constants. LINEST(A2:A8;B2:C8;1;1) returns appropriate statistics for that equation.
Issues:
- You need a good understanding of the statistics involved.
- Empty cells in the output array show #N/A (in Calc and Excel).