LOGEST

Syntax:

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:

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.]