How to Use the GROWTH Function in Google Sheets to Calculate Exponential Trend Forecasting

When forecasting business metrics like digital product adoption, viral user signups, or compounding interest, assuming a constant linear increase (as performed by the TREND function) often underestimates outcomes. Real-world technological and economic adoption frequently follows exponential curves. The GROWTH function in Google Sheets fits an exponential growth curve to historical data, projecting future values based on compounding trajectory models.

Syntax and Parameters of the GROWTH Function

The syntax for GROWTH accepts four parameters:

=GROWTH(known_data_y, [known_data_x], [new_data_x], [b])

• known_data_y: The array or range of dependent values already observed (e.g., monthly revenue figures in B2:B12).
• known_data_x: The optional range of independent variables corresponding to known_data_y (e.g., month numbers 1 through 11 in A2:A12). If omitted, Google Sheets assumes a default sequential series 1, 2, 3....
• new_data_x: The range of target independent time points for which you want to predict values (e.g., month numbers 12 through 15 in A13:A16).
• b: An optional boolean flag controlling curve calculation. If set to TRUE (or omitted), the base intercept b is calculated normally. If FALSE, b is forced to 1 (representing the mathematical curve y = m^x).

Step-by-Step Example: Forecasting Compounding Revenue

Suppose you manage an e-commerce brand that recorded monthly revenue across its first 6 months:

• Column A contains months: 1, 2, 3, 4, 5, 6 (cells A2:A7).
• Column B contains revenue: $10,000, $12,500, $16,000, $21,000, $27,500, $36,000 (cells B2:B7).
• Column A continues with future target months: 7, 8, 9 (cells A8:A10).

To forecast revenue for Month 7 in cell B8, enter:

=GROWTH(B2:B7, A2:A7, A8)

The formula projects approximately $46,900 for Month 7, capturing the accelerating month-over-month growth rate rather than a flat linear average.

Generating Multi-Period Array Projections

Instead of copying individual formulas down the column, GROWTH operates natively as an dynamic array formula. Select cells B8:B10 and enter the full future range as new_data_x:

=GROWTH(B2:B7, A2:A7, A8:A10)

Google Sheets will automatically spill the predicted figures down rows 8, 9, and 10.

GROWTH vs TREND: Choosing the Correct Forecasting Model

• Use TREND: When data grows by a fixed quantity per period (e.g., gaining roughly 50 new subscribers every week). Linear regression suits stable, mature processes with fixed output capacity.
• Use GROWTH: When data grows by a fixed percentage rate per period (e.g., expanding by 15% each month). Exponential curves model viral loops, computing capacity scaling, and compounding interest investments.

Common Errors and Troubleshooting

• Negative or Zero Y-Values: The GROWTH function relies on logarithmic transformations (ln(y)). If any value in known_data_y is zero or negative, the formula returns a #NUM! calculation error. Ensure your source data contains strictly positive numbers.
• Mismatched Range Dimensions: Ensure known_data_x and known_data_y span the exact same number of rows or columns to avoid #REF! errors.