[Table of Contents] [docx version]
SpreadsheetML Reference Material - Table of Contents
LOGEST ( known-ys [ , [ known-xs ] [ , [ const-flag ] [ , stats-flag ] ] )
Description: Calculates an exponential curve that fits the data, and returns an array of values that describes the curve.
Mathematical Formula:
The equation for the curve is:
y = b*mx
or
y = (b*(m1x1)*(m2x2)*…) (if there are multiple x-values)
where the dependent y-value is a function of the independent x-values. The m-values are bases corresponding to each exponent x-value, and b is a constant value. Note that y, x, and m can be vectors.
When there is only one independent x-variable, the y-intercept (b) values can be obtained directly by using the following formula:
Y-intercept (b): INDEX(LOGEST(known-ys,known-xs),2)
Arguments:
|
Name |
Type |
Description |
|
known-ys |
array |
The set of y-values already known in the relationship y=b*mx. If the array known-ys is a single column, then each column of known-xs is interpreted as a separate variable. If the array known-ys is a single row, then each row of known-xs is interpreted as a separate variable.
|
|
known-xs |
array |
An optional set of x-values that might already be known in the relationship y=b*mx. The array known-xs can include one or more sets of variables. If only one variable is used, known-ys and known-xs can be ranges of any shape, as long as they have equal dimensions. If more than one variable is used, known-ys shall be a vector (that is, a range with a height of one row or a width of one column). If known-xs is omitted, it is assumed to be the array {1,2,3,...} that is the same size as known-ys. |
|
const-flag |
logical |
Specifies whether to force the constant b to be 1. If TRUE or omitted, b is calculated normally. If FALSE, b is set to 1, and the m-values are adjusted to fit y=mx. |
|
stats-flag |
logical |
Specifies whether to return additional regression statistics. If TRUE, LOGEST returns the additional regression statistics, so the returned array is {mn, mn-1, ..., m1, b; sen, sen-1, ..., se1, seb; r2, sey; F, df; ssreg, ssresid}. If FALSE or omitted, LOGEST returns only the m-coefficients and the constant b. |
The additional regression statistics are described in §3.17.7.193.
Return Type and Value: array – The array that describes the line, in the form {mn, mn-1, ..., m1, b}. The order in which the additional regression statistics are returned is described in §3.17.7.193.
[Example: Given the following data:
|
|
A |
B |
|
1 |
Month |
Units |
|
2 |
11 |
33,100 |
|
3 |
12 |
47,300 |
|
4 |
13 |
69,000 |
|
5 |
14 |
102,000 |
|
6 |
15 |
150,000 |
|
7 |
16 |
220,000 |
|
8 |
Formula |
|
|
9 |
1.463275628 |
495.3047702 |
When LOGEST(B2:B7,A2:A7,TRUE,FALSE) is array-entered into cells A9:B9, those cells take on the results shown.
end example]