Practice free

FORECAST.ETS function

FORECAST.ETS projects a future value from a time series, allowing for trend and repeating seasonal patterns. It suits sales or demand forecasting and needs a timeline with a consistent interval.

Example

Practice this in the app

Syntax

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

Arguments in [square brackets] are optional.

target_date (required)
The date or number to forecast for.
values (required)
Historical values.
timeline (required)
Dates or numbers with a constant step.
seasonality (optional)
1 auto-detect (default), 0 none, or a season length.
data_completion (optional)
1 fills gaps by interpolation (default), 0 treats gaps as zero.
aggregation (optional)
How to combine duplicate timestamps; 1 is average.

Returns: A forecast value from exponential triple smoothing.

What Excel actually returns

Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.

FormulaExcel showsCase
=FORECAST.ETS(4,{1,2},{1,2}) 4 Too few points
=FORECAST.ETS(DATE(2025,1,1),Data!AF1:AF24,Data!AE1:AE24,1) 125 Working example
=FORECAST.ETS(DATE(2025,1,1),Data!AF1:AF24,Data!AE1:AE24) 125 Auto seasonality
=FORECAST.ETS(DATE(2025,1,1),Data!AF1:AF24,Data!AE1:AE24,0) 144.7038525 No seasonality
=FORECAST.ETS(DATE(2025,1,1),Data!AF1:AF24,Data!AE1:AE24,4) 125 Explicit seasonality 4
=FORECAST.ETS(DATE(2023,6,1),Data!AF1:AF24,Data!AE1:AE24) 116 Target inside history
=FORECAST.ETS(DATE(2022,1,1),Data!AF1:AF24,Data!AE1:AE24) #NUM! Target before start
=FORECAST.ETS(DATE(2025,1,1),Data!AF1:AF23,Data!AE1:AE24) #N/A Different sizes

The Data sheet these formulas read

A cell that starts with = holds that formula.

Watch out for

  • Timeline needs a consistent interval.
  • Fails on duplicate or unsorted timestamps unless aggregation handles them.
  • Results come from an algorithm, so hand-checking is hard.

Where it works

Excel
FORECAST.ETS arrived in Excel 2016. Older versions do not have it. Reported as unavailable in some Excel for Mac versions; check your build.
Google Sheets
No direct equivalent. Sheets has no equivalent; FORECAST or TREND give only straight-line forecasts.

Part of Statistical functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .