How to Use the FORECAST.ETS Functions in Google Sheets for Seasonal Time Series Forecasting

Linear trend forecasting tools like FORECAST.LINEAR or TREND work well for datasets that follow a constant upward or downward trajectory. However, real-world business data—such as quarterly retail sales, holiday server demand, or seasonal travel bookings—exhibits cyclical peaks and troughs. Applying basic linear regression to cyclical time series leads to inaccurate projections that ignore historical seasonality.

To address this complexity, Google Sheets provides the FORECAST.ETS family of functions. Built upon the AAA version of Holt-Winters Exponential Triple Smoothing (ETS), these functions weigh recent observations, compute repeating cyclical rhythms, and output robust predictive forecasts along with statistical confidence bounds.

Overview of the FORECAST.ETS Suite

Google Sheets offers four distinct ETS functions tailored to different predictive dimensions:

Function Name	Core Output	Primary Practical Application
FORECAST.ETS	Predicted numeric value	Forecasts future timeline metrics using additive seasonal exponential smoothing.
FORECAST.ETS.CONFINT	Confidence margin (+/-)	Calculates a statistical tolerance interval for a specified certainty level (e.g. 95%).
FORECAST.ETS.SEASONALITY	Detected cycle length	Identifies repeating periodic patterns across your historical timeline (e.g. 4 quarters, 12 months).
FORECAST.ETS.STAT	Smoothing coefficient (0-1)	Returns internal ETS model parameters (Alpha, Beta, Gamma, RMSE, MAE, or MAPE).

Syntax and Parameters of FORECAST.ETS

The standard syntax for predicting a seasonal datapoint is:

=FORECAST.ETS(target_date, historical_values, timeline, [seasonality], [data_completion], [aggregation])

• target_date: The future date or numeric timeline point you wish to forecast.
• historical_values: The range of past metrics (e.g., revenue, user visits, shipments).
• timeline: The corresponding range of chronological dates. Dates must have consistent step intervals (daily, weekly, monthly, quarterly).
• seasonality (Optional): The recurring cycle length. Set to 1 (default) to let Google Sheets auto-detect seasonality, or enter an explicit integer (e.g. 12 for monthly data with annual cycles, or 4 for quarterly data). Set to 0 for non-seasonal linear smoothing.
• data_completion (Optional): Handles missing dates. 1 (default) interpolates missing values using neighbour averages; 0 treats gaps as zero.
• aggregation (Optional): Handles duplicate timestamps. Default 1 calculates the average; other options include SUM (2), COUNT (3), MAX (5), and MIN (6).

Step-by-Step Implementation: Forecasting Quarterly Sales

Consider a sales ledger spanning 3 full years (12 historical quarters from Q1 2023 to Q4 2025):

• Column A (Dates): A2:A13 contains quarter-ending dates (e.g. 31/03/2023 through 31/12/2025).
• Column B (Revenue): B2:B13 contains historical quarterly sales figures showing steady annual Q4 holiday surges.
• Target Date in A14: 31/03/2026 (Q1 2026).

To project Q1 2026 sales accounting for past seasonal trends, enter this formula in cell B14:

=FORECAST.ETS(A14, $B$2:$B$13, $A$2:$A$13, 4, 1, 1)

Here, specifying 4 indicates that sales repeat on a 4-quarter cycle. Google Sheets isolates baseline trend growth from the regular Q1 seasonal contraction, producing a much more realistic forecast than basic linear regression.

Constructing Upper and Lower Confidence Intervals

Forecasts inherently carry variance. To visualise best-case and worst-case scenarios, calculate an error bound using FORECAST.ETS.CONFINT.

To find the 95% confidence tolerance for your Q1 2026 projection, enter in cell C14:

=FORECAST.ETS.CONFINT(A14, $B$2:$B$13, $A$2:$A$13, 0.95, 4)

With the margin established in cell C14, define your high and low bounds:

• Upper Confidence Bound: =B14 + C14
• Lower Confidence Bound: =B14 - C14

Plotting the forecast along with these bounds in a Line Chart provides leadership with an immediate risk envelope for future operational planning.

Troubleshooting FORECAST.ETS Formula Errors

If your ETS formula returns calculation errors, verify these frequent data format issues:

• #VALUE! Error: Occurs when the timeline range contains unparsed text dates or strings. Always ensure dates are valid spreadsheet date serials formatted using DATE().
• #NUM! Error: Triggered when the historical date range lacks a discernible regular step size (e.g. mixing random days with monthly records), or when less than two full seasonal cycles are available for analysis. Ensure you provide at least 2 full periods (e.g. minimum 24 months for monthly seasonality).
• Unbalanced Ranges: The historical_values and timeline ranges must contain an identical count of cells (e.g. B2:B50 paired with A2:A50). Mismatched ranges throw instant formula evaluation faults.