Practice free

LOGEST function

LOGEST fits an exponential curve to your data and returns an array with the growth factor per x-step and the starting value. Use it for things that grow by a steady percentage.

Example

Read it as a sentence: fit an exponential curve of the L1:L5 values against F1:F5 and return the growth factor, which gives 2.

Practice this in the app

Syntax

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

Arguments in [square brackets] are optional.

known_y's (required)
The y values you already have; must all be positive for an exponential fit.
known_x's (optional)
The matching x values. Leave out to use 1, 2, 3, ...
const (optional)
TRUE (default) fits the intercept b normally; FALSE forces b to equal 1.
stats (optional)
TRUE returns extra regression statistics under the coefficients; FALSE (default) returns just the coefficients.

Returns: An array holding the growth multiplier(s) m and the constant b for the curve y = b*m^x.

One LOGEST formula can fill more than one cell. When the result has several values, they spill into the cells below or to the right of the formula.

What Excel actually returns

Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.

FormulaExcel showsCase
=LOGEST(Data!L1:L5,Data!F1:F5) 2 Full result (spills)
=LOGEST(Data!L1:L5) 2 Y only
=LOGEST(Data!L1:L5,Data!F1:F5,FALSE) 2 Const FALSE
=LOGEST(Data!L1:L5,Data!F1:F5,TRUE,TRUE) 2 Stats TRUE (spills 5x2)
=LOGEST(Data!L1:L5,Data!J1:J5) 1 Constant x
=LOGEST(Data!L1:L5,CHOOSE({1,2},Data!F1:F5,Data!F1:F5^2)) 1 Two x columns
=LOGEST(Data!L1:L5,Data!K1:K3) #REF! Different sizes
=LOGEST(Data!B1:B5,Data!F1:F5) #VALUE! Text in y

The Data sheet these formulas read

A cell that starts with = holds that formula.

Watch out for

  • Returns an array, so older Excel needs Ctrl+Shift+Enter; wrap in INDEX to pull out a single number.
  • Any zero or negative y value produces #NUM!.
  • The multiplier is returned first (m), then b; with several x columns the multipliers come in reverse order.

Where it works

Excel
LOGEST is in every version of Excel from 2007 on.
Google Sheets
Same name, same behaviour for everyday use. Same arguments and output layout.

Part of Statistical functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .