Related Function:

The LOGEST function calculates an exponential curve of best fit, through a supplied set of x- and y- values.

Syntax

=LOGEST(known_y’s,[known_x’s],[const],[stats])

Arguments

Argument Description
known_y’s The set of y-values you already know in the relationship y = b*m^x

  If the array known_y’s is in a single column, then each column of [known_x’s] is interpreted as a separate variable
  If the array known_y’s is in a single row, then each row of [known_x’s] is interpreted as a separate variable
[known_x’s] Optional. An optional set of x-values that you may already know in the relationship y = b*m^x

  If provided the [known_x’s] array should have the same length as the known_y’s array
  If omitted, the [known_x’s] array takes on the default value {1, 2, 3, …} that is the same size as known_y’s
  Quarterly
[const] Optional. A logical value specifying whether to force the constant b to equal 1

  TRUE
(or omitted)
b is calculated normally
  FALSE b is set equal to 1, and the m-values are fitted to y = m^x
[stats] Optional. A logical value specifying whether to return additional regression statistics

  TRUE 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}
  FALSE
(or omitted)
returns only the m-coefficients and the constant b

The additional regression statistics are as follows:

  Statistic Description
  se1,se2, … ,sen The standard error values for the coefficients m1,m2, … ,mn
  seb The standard error value for the constant b – seb = #N/A when [const] is FALSE
  r2 The coefficient of determination
  sey The standard error for the y estimate
  F The F statistic, or the F-observed value
  df The degrees of freedom
  ssreg The regression sum of squares
  ssresid The residual sum of squares

Note: Because LOGEST returns an array, it must be entered as an array formula. If the function is not entered as an array formula, only the first ‘m’ value in the calculated array of statistical information is returned.

Examples

  A B C
1 Known (Xs) Known (Ys)  
2 12 15  
3 17 18  
4 14 27  
5 13 16  
6      
7 Result Array Formula entered in cells A8:B8
8 1.03506 11.40767 {=LOGEST(B2:B5,A2:A5,TRUE,FALSE)}

Common Function Error(s)

Problem What went wrong
#VALUE! Occurs if either:

  any of the values in the supplied [known_x’s] or known_y’s arrays are not numeric values – this may include text representations of numbers which the LOGEST function does not recognize as numbers
  either of the [const] or [stats] arguments can not be evaluated to TRUE or FALSE
#REF! Occurs if the array of [known_x’s] is not the same length as the array of known_y’s

The LOGEST function calculates the exponential curve that best fits a supplied set of y- and x-values. If there is a single range of x-values, the calculated exponential curve satisfies the equation:  

    \[    y = bm^x    \]

where,

  • x is the independent variable
  • y is the dependent variable
  • m is a constant base for the x value
  • b is a constant which is the value of y when x = 0

If there are multiple ranges of x-values, the calculated exponential curve satisfies the following equation:

    \[    y = bm_1^{x_1} m_2^{x_2} ...    \]

where,

  • the x’s are the independent variable ranges
  • y is the dependent variable
  • the m’s are constant bases for the x values
  • b is a constant