How to Use the TREND and GROWTH Functions in Google Sheets for Predictive Forecasting

Whether forecasting quarterly product revenue, projecting website visitor trends, or modelling bacterial growth curves, data analysts often need to extrapolate future values from historical time-series datasets. While Google Sheets lets you add trendlines visually inside chart editors, integrating predictions directly into financial calculations and dynamic reports requires formula-based modelling. The native TREND and GROWTH array functions provide statistical engines for calculating linear least-squares regressions and exponential compounding forecasts without manual curve fitting.

Understanding the Difference Between TREND and GROWTH

Choosing between these two functions depends on the mathematical trajectory of your dataset:

• TREND (Linear Regression): Assumes that the dependent variable ($y$) changes at a constant additive rate per interval, fitting the linear equation $y = mx + b$. It is ideal for mature business lines, predictable monthly costs, and stable headcount projections.
• GROWTH (Exponential Regression): Assumes that the dependent variable ($y$) changes at a constant percentage or multiplicative rate per interval, fitting the exponential curve $y = b \cdot m^x$. It is ideal for viral user growth, compound interest, technology adoption, and early-stage startup sales.

Part 1: The TREND Function (Linear Projections)

The TREND function calculates linear trend values based on the method of least squares.

Syntax

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

Parameter Definitions

• known_data_y (Required): The array of dependent historical observations (e.g. past revenue or units sold).
• known_data_x (Optional): The array of independent variables (e.g. month numbers or chronological dates). If omitted, Sheets assumes {1, 2, 3...}.
• new_data_x (Optional): The future independent values for which you want to predict corresponding $y$-values.
• b (Optional, Default: TRUE): A boolean flag. If TRUE, the constant intercept is calculated normally; if FALSE, the intercept $b$ is forced to zero.

Practical TREND Example

Suppose you have historical sales data across 6 quarters in cells A2:B7:

• Column A (A2:A7): Quarter indices {1, 2, 3, 4, 5, 6}
• Column B (B2:B7): Sales revenue {10000, 12500, 14800, 17200, 19500, 22000}
• Column A (A8:A11): Target future quarters {7, 8, 9, 10}

To predict sales for Quarters 7 through 10, enter the following formula in cell B8:

=TREND(B2:B7, A2:A7, A8:A11)

Because Google Sheets supports dynamic arrays, the formula automatically spills predicted linear values across cells B8:B11 in a single calculation.

Part 2: The GROWTH Function (Exponential Projections)

The GROWTH function fits an exponential curve through historical data points to model compounding expansion or decay.

Syntax

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

Parameter Definitions

• known_data_y (Required): The historical values showing exponential acceleration. Values must be strictly greater than zero ($y > 0$).
• known_data_x (Optional): The timeline or predictor intervals corresponding to historical values.
• new_data_x (Optional): The future timeline points you wish to forecast.
• b (Optional, Default: TRUE): If FALSE, forces the constant base to 1.

Practical GROWTH Example

Assume an online platform records compounding user registration numbers:

• Column A (A2:A6): Months {1, 2, 3, 4, 5}
• Column B (B2:B6): Active users {1200, 1850, 2900, 4400, 6800}
• Column A (A7:A9): Target forecast months {6, 7, 8}

To project exponential user counts for months 6, 7, and 8, enter in cell B7:

=GROWTH(B2:B6, A2:A6, A7:A9)

Google Sheets immediately outputs the forecasted exponential curve across the downstream rows.

Step 3: Calculating In-Sample Fitted Values for Visual Comparison

To evaluate how well either model fits your historical data, omit the new_data_x parameter:

=TREND(B2:B7, A2:A7)

Sheets generates fitted baseline values alongside your raw observations, allowing you to plot both actuals and model lines on a dual-line chart to evaluate goodness of fit.

Key Operational Rules and Troubleshooting

Keep these statistical and mathematical boundaries in mind when structuring models:

• Matching Array Dimensions: known_data_y and known_data_x must contain identical numbers of data rows. Mismatched ranges result in a #VALUE! error.
• Zero and Negative Numbers in GROWTH: Because exponential math relies on natural logarithms ($\ln(y)$), any zero or negative value inside known_data_y causes GROWTH to fail with a #NUM! error.
• Array Spill Clearances: Ensure the cells directly below your formula are completely empty. Any preexisting numbers or text will block array expansion, returning a #REF! spill collision error.