Difference between revisions of "Documentation/How Tos/Calc: LOGEST function"
From Apache OpenOffice Wiki
< Documentation | How Tos
m (→Example:) |
OOoWikiBot (Talk | contribs) m (Robot: Automated text replacement %s) |
||
| Line 16: | Line 16: | ||
: if <tt>'''allow_const'''</tt> is <tt>'''FALSE'''</tt> the constant ''a'' is forced to be one; ''y'' = ''b<sup>x</sup>''. If omitted, <tt>'''allow_const'''</tt> defaults to <tt>'''TRUE'''</tt> (a value for ''a'' is found). | : if <tt>'''allow_const'''</tt> is <tt>'''FALSE'''</tt> the constant ''a'' is forced to be one; ''y'' = ''b<sup>x</sup>''. If omitted, <tt>'''allow_const'''</tt> defaults to <tt>'''TRUE'''</tt> (a value for ''a'' is found). | ||
| − | : <tt>'''LOGEST'''</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>'''LOGEST'''</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. The statistics relate to the equation for ln(''y'') given above. | : 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. The statistics relate to the equation for ln(''y'') given above. | ||
| Line 26: | Line 26: | ||
: ''σ<sub>1</sub>'' to ''σ<sub>n</sub>'' are the standard error values for the ln(''b'') values; ''σ<sub>a</sub>'' is the standard error value for the ln(''a'') value. | : ''σ<sub>1</sub>'' to ''σ<sub>n</sub>'' are the standard error values for the ln(''b'') values; ''σ<sub>a</sub>'' is the standard error value for the ln(''a'') value. | ||
| − | :''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 ln(''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 ln(''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 41: | Line 41: | ||
:In the example above, cells A2:B6 contain the ''x'',''y'' values for a set of points. <tt>'''LOGEST(B2:B6;A2:A6;1;1)'''</tt> returns the statistics for a best fit exponential curve through those points. | :In the example above, cells A2:B6 contain the ''x'',''y'' values for a set of points. <tt>'''LOGEST(B2:B6;A2:A6;1;1)'''</tt> returns the statistics for a best fit exponential curve through those points. | ||
| − | + | {{Documentation/SeeAlso| | |
| − | [[Documentation/How_Tos/Calc: LINEST function| | + | * [[Documentation/How_Tos/Calc: LINEST function|LINEST]] |
| − | [[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. | ||
* Any empty cells in the output array show <tt>'''#N/A'''</tt> (in Calc and Excel). | * Any empty cells in the output array show <tt>'''#N/A'''</tt> (in Calc and Excel). | ||
Revision as of 14:02, 25 February 2009
LOGEST
Returns a table of statistics for an exponential curve that best fits a data set.
Syntax:
LOGEST(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 x variables, xvalues may be a range with corresponding multiple rows or columns.
- LOGEST finds an exponential curve y = a . bx that best fits the data. With more than one set of variables the curve is of the form y = a . b1x1 . b2x2 ... . bnxn.
- In order to fit the curve, LOGEST uses linear regression (the "least squares" method) based on the equation ln(y) = ln(a) + x1ln(b1) + x2ln(b2) + ... xnln(bn).
- if allow_const is FALSE the constant a is forced to be one; y = bx. If omitted, allow_const defaults to TRUE (a value for a is found).
- LOGEST 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. The statistics relate to the equation for ln(y) given above.
- b1 to bn and a are the coeficients for the equation above.
- σ1 to σn are the standard error values for the ln(b) values; σa is the standard error value for the ln(a) value.
- r2 is the determination coefficient (* RSQ); σy is the standard error value for the ln(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:B6 contain the x,y values for a set of points. LOGEST(B2:B6;A2:A6;1;1) returns the statistics for a best fit exponential curve through those points.
Template:Documentation/SeeAlso
Issues:
- You need a good understanding of the statistics involved.
- Any empty cells in the output array show #N/A (in Calc and Excel).
