How to Use the LOGEST Function in Google Sheets for Exponential Trend Fitting and Growth Forecasting

While linear regression assumes that a dependent variable changes by a constant absolute amount for every unit increase in an explanatory variable, real-world phenomena rarely behave in a strictly linear manner. Technology adoption, biological cell proliferation, infectious disease transmission, and financial compound interest follow non-linear exponential trajectories where values multiply by a constant percentage rate over time. In Google Sheets, modelling this multiplicative behaviour is handled through the dedicated exponential regression function: LOGEST (Logarithmic Estimation).

Understanding Exponential Curve Fitting with LOGEST

The LOGEST function calculates an exponential curve that best fits your historical observations using the mathematical formula:

y = b * (m1^x1) * (m2^x2) * ... * (mn^xn)

Where $y$ is the dependent response variable, $b$ is the base constant ($y$-intercept when $x = 0$), $m_1 dots m_n$ are the base multipliers for each independent variable $x$, and $x_1 dots x_n$ are your explanatory predictors.

The calculated base multiplier $m$ reveals the compounding growth rate directly:

• If $m > 1$: Indicates exponential growth. The percentage growth rate per period equals $(m – 1) imes 100%$. For example, an $m$ of 1.15 represents a 15% increase per unit period.
• If $m < 1$: Indicates exponential decay. The rate of decay equals $(1 – m) imes 100%$. An $m$ of 0.85 reflects a 15% loss per period.
• If $m = 1$: Represents flat, stationary output with zero compounding change.

Output Row	Left Column(s)	Rightmost Column
Row 1	Base multipliers ($m_n, m_{n-1}, dots, m_1$)	Base constant ($b$)
Row 2	Standard error for multipliers ($se_n dots se_1$)	Standard error for base constant ($se_b$)
Row 3	Coefficient of determination ($R^2$)	Standard error for the $y$ estimate ($se_y$)
Row 4	$F$-statistic ($F$)	Degrees of freedom ($df$)
Row 5	Regression sum of squares ($ss_{reg}$)	Residual sum of squares ($ss_{resid}$)

Syntax and Argument Rules

=LOGEST(known_data_y, [known_data_x], [calculate_b], [verbose])

• known_data_y: The range of observed response values. Strict Requirement: Every value in known_data_y must be strictly greater than zero ($y > 0$) because logarithms cannot evaluate zero or negative numbers.
• known_data_x: Optional range of predictor values. If omitted, Google Sheets defaults to the integer sequence {1, 2, 3, ...} matching the length of $y$.
• calculate_b: Optional boolean. If TRUE (or omitted), $b$ is calculated normally. If FALSE, $b$ is forced to 1 ($y = m^x$).
• verbose: Optional boolean. If TRUE, spills the full 5-row regression diagnostics matrix. If FALSE (default), returns only the base multiplier $m$ and constant $b$.

Step-by-Step Example: Cloud Service User Growth

Suppose a technology startup records its active user base over its first 6 quarters in cells A2:B7:

Quarter (X) — Col A	Active Users (Y) — Col B
1	1,250
2	1,820
3	2,650
4	3,910
5	5,720
6	8,410

1. Calculating the Growth Multiplier

In cell D2, enter:

=LOGEST(B2:B7, A2:A7, TRUE, FALSE)

Google Sheets outputs two values across D2:E2:

• Cell D2 (Multiplier $m$): 1.465
• Cell E2 (Constant $b$): 851.3

This demonstrates that the user base is compounding at an average quarterly rate of 46.5% ($1.465 – 1$).

2. Projecting Future Quarters

To forecast active users for Quarter 8 ($x = 8$), construct the projection formula:

=E2 * (D2^8)

Result: 18,124 users.

Troubleshooting Common LOGEST Errors

• #NUM! Error (Zero or Negative Values): Because LOGEST applies logarithmic transformation ($ln(y)$) to linearise data points during least-squares evaluation, any value where $y le 0$ triggers an immediate #NUM! failure. If your dataset contains zeros, consider shifting values by adding a small positive constant (e.g. $y + 1$).
• #REF! Error (Spill Obstruction): If you set verbose to TRUE, ensure a 5-row by 2-column grid area below and to the right of your formula cell is completely clear of text or formatting.
• Mismatch Between LINEST and LOGEST: If your calculated $R^2$ in LOGEST appears lower than expected, verify that your data genuinely reflects percentage compounding rather than arithmetic addition. For constant incremental additions, use LINEST instead.