The LOGEST function in Excel fits an exponential curve to data and returns its coefficients, with optional regression statistics.
The coefficients describe the fitted growth pattern and can be used to calculate predicted values. LOGEST can also work with more than one predictor.
Its output is a block of results, so reading the coefficient order matters when using them in another calculation.
In this article, I’ll show you how to use fitted coefficients to forecast values, return regression statistics, and model two predictor variables.
LOGEST Function Syntax in Excel
The LOGEST function uses the following syntax:
=LOGEST(known_y's,[known_x's],[const],[stats])
- known_y’s: Required. The positive y-values used to fit the exponential curve.
- known_x’s: Optional. The x-values paired with known_y’s. If omitted, Excel uses 1, 2, 3, and so on.
- const: Optional. TRUE or omitted calculates the starting constant b. FALSE forces b to equal 1.
- stats: Optional. FALSE or omitted returns only the coefficients. TRUE also returns standard errors and regression statistics.
For one predictor, LOGEST fits the equation y = b*m^x. It returns m first, followed by b.
When to Use LOGEST Function
- Find the growth base and starting value for an exponential pattern.
- Build forecasts from the fitted exponential coefficients.
- Review fit statistics and coefficient uncertainty.
- Fit an exponential model with several predictor variables.
- Force the starting constant to 1 when the model requires it.
Example 1: Return m and b
Here is a clean exponential series that makes the two coefficients easy to see.
Below are active subscriptions from month 0 through month 4.
I want to find the growth base m and starting value b.
Enter this formula in cell D2:
=LOGEST(B2:B6,A2:A6,TRUE,FALSE)
The formula spills 1.2000 into D2 and 50.0000 into E2.
That gives the fitted equation y = 50*1.2^x. The starting value is 50, and each one-unit increase in x multiplies y by 1.2.
Pro Tip: Keep E2 empty before entering the formula. A value in the spill range causes a #SPILL! error.
Example 2: Forecast From LOGEST Coefficients
Once you have m and b, you can plug them into the fitted equation to project new values.
Below are the same subscription values, three future months, and spaces for the coefficients and forecasts.
I want to return the coefficients first, then forecast months 5 through 7.
Enter this formula in cell G2:
=LOGEST($B$2:$B$6,$A$2:$A$6,TRUE,FALSE)
LOGEST spills 1.2000 into G2 and 50.0000 into H2.
Next, enter this forecast formula in cell E2 and fill it down through E4:
=$H$2*$G$2^D2
The forecast values are 124.4160, 149.2992, and 179.1590.
The absolute references keep m and b fixed while D2 changes to D3 and D4. Each row evaluates b*m^x for its forecast month.
GROWTH can return projected y-values directly. This LOGEST approach is useful when you also want to see and reuse the fitted coefficients.
Example 3: Return Full LOGEST Statistics
Set stats to TRUE when you need more than the coefficients.
Below is a slightly uneven subscription series and a labelled 5-by-2 output area.
I want Excel to return the coefficients, standard errors, and regression statistics.
Enter this formula in cell E2:
=LOGEST(B2:B7,A2:A7,TRUE,TRUE)
The formula fills E2:F6 with these results:
- Row 2: m is 1.2107, and b is 50.0624.
- Row 3: The log-scale coefficient standard errors are 0.0073 and 0.0222.
- Row 4: R-squared is 0.9942, and the log-scale standard error for y is 0.0306.
- Row 5: The F statistic is 680.9258, with 4 degrees of freedom.
- Row 6: Regression sum of squares is 0.6397, and residual sum of squares is 0.0038 on the log scale.
LOGEST performs a linear regression on transformed values behind the scenes. That is why the standard errors and sums of squares relate to the log-scale model.
Use LINEST when the relationship itself is linear. LOGEST is for an exponential curve.
Example 4: Fit Two Predictor Variables
LOGEST can fit a model with several independent variables.
Below are qualified leads measured against ad campaigns and email sends in thousands.
I want the exponential coefficients for both predictors and the starting constant.
Enter this formula in cell E2:
=LOGEST(C2:C6,A2:B6,TRUE,FALSE)
The formula returns 1.2000 for email sends, 1.1000 for ad campaigns, and 10.0000 for b.
LOGEST returns the predictor coefficients in reverse column order. Column B’s coefficient appears first, followed by column A’s coefficient and then b.
The fitted equation is y = 101.1^x11.2^x2, where x1 is ad campaigns and x2 is email sends.
Example 5: Force b to Equal 1
The const argument can force the starting constant b to equal 1.
Below is a perfect power series in which each period multiplies units by 3.
I want to fit y = m^x and return only m.
Enter this formula in cell D2:
=INDEX(LOGEST(B2:B6,A2:A6,FALSE,FALSE),1,1)
The formula returns 3.0000.
FALSE forces b to 1. INDEX extracts the first value from the LOGEST result, which is the fitted growth base m.
Use const FALSE only when a starting value of 1 makes sense for the model. Otherwise, let LOGEST estimate b.
Tips & Common Mistakes
- Keep known_y’s positive. LOGEST fits the natural logarithms of the y-values, so zero or negative values cannot be used in the fit.
- LOGEST returns coefficients in reverse predictor order when known_x’s has several columns.
- Keep the complete spill area empty in Excel 2021 and later.
- In Excel 2019 and earlier, select the complete output range and confirm the formula with Ctrl+Shift+Enter.
- Set stats to TRUE only when you need the five-row statistics array. Its width is the number of predictors plus one column for b.
- The
^operator raises m to each x value. The POWER function can perform the same exponent step. - Treat forecasts beyond the fitted y-value range with care. Microsoft notes that extrapolated results may not remain valid.
I covered LOGEST coefficients, forecasts, fit statistics, multiple predictors, and the const argument.
I hope you found this article helpful.
Other Excel articles you may also like:
