For business users of Microsoft Excel Free guides and templates
Home >  Excel Help >  Excel Functions >  

Statistical

LOGEST Function

Calculates an exponential curve that fits your data and returns an array that describes the curve.


by Charley Kyd, MBA
Microsoft Excel MVP, 2005-2014
The Father of Spreadsheet Dashboard Reports

The LOGEST function fits an exponential curve—that is, a growth-rate curve—to your data and returns one or more values that describe the curve. When you want more than one value, you must enter it as an array or your must include it in an INDEX function.

Syntax

LOGEST(known_y's, known_x's, const, stats)

  • known_y's  Required. The range or array of y values you already know in the relationship y = b*m^x. (From another perspective, a chart typically displays the same "y" values in the vertical axis.) 

  • known_x's  Optional. The range or array of x values you typically know in the relationship y = b*m^x. (From another perspective, "x" values are frequently date serial numbers, counters, or other numeric categories.) If 'known_x's' is omitted, it is assumed to be the array {1,2,3,...} that is the same size as 'known_y's'.

  • const  Optional. A logical value that specifies whether to force the constant b to equal 1.

    • If const is TRUE or omitted, then b is calculated normally.

    • If const is FALSE, b is set equal to 1, and the m-values are fitted to y = m^x

  • stats  Optional. A logical value that specifies whether to return additional regression statistics.

    • If stats is FALSE or omitted, LOGEST returns only the m-coefficients and the constant b, as shown in the first two rows of the following table.

    • If stats is TRUE, LOGEST returns these regression statistics as an array:

      Row Col Name Description
      1 1 m The growth rate of the curve
      1 2 b The y-intercept (which is the y-intercept) of the curve
      2 1 se The standard error value for m
      2 2 seb The standard error value for b
      3 1 R2 The coefficient of determination
      3 2 sey The standard error of the y estimate
      4 1 F The F statistic
      4 2 df The degrees of freedom
      5 1 ssreg The regression sum of squares
      5 2 ssresid The residual sum of squares
       

Applies To

Excel 2003 and above

Remarks

When you have only one independent x-variable, and if you enter a LOGEST formula normally, it returns the "m" value, which is equal to one plus the growth rate of the fitted curve.

When you have only one independent x-variable, you can obtain y-intercept (b) values directly by using the formula...

=INDEX(LOGEST(known_y's,known_x's),2)

Similarly, this formula also will return the y-intercept (b) values...

=GROWTH(known_y's, known_x's, 0)

Examples

The following examples use this data:

Other ExcelUser Information

Other Help

 

Charley's SwipeFile charts


Free Excel Dashboards