FORECAST.ETS Function

Forecasts a future value from a time series, including trend and seasonality.

Category

Statistical

Available in

Excel 2016+, Microsoft 365

Returns

A number

Taught hands-on in Data Analytics · Relationships & Trends module

Overview

FORECAST.ETS uses Excel's AAA version of exponential triple smoothing: it tracks the level, the trend and a repeating seasonal pattern, giving recent history more weight than older history.

Give it a target date (or period number) after the end of your history, the historical values and the matching timeline. It's the function behind Data › Forecast Sheet.

The timeline must be numbers or dates with a constant step (every day, every 7 days, the 1st of every month …). It doesn't need to be sorted, and up to 30% of the points can be missing; data_completion decides whether gaps are filled by averaging neighbours (1, the default) or treated as zero (0). Duplicate dates are combined with the aggregation method (default AVERAGE).

Syntax

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

Arguments

ArgumentRequired?What it does
target_dateYesThe date or period to forecast; must come after the last point of the timeline.
valuesYesThe historical values.
timelineYesThe dates or period numbers for the values, with a constant step.
seasonalityNo1 (default) detects the season length automatically, 0 means no seasonality, and a whole number up to 8,760 sets the length yourself (4 for quarters, 12 for months).
data_completionNo1 (default) fills missing points with the average of their neighbours; 0 treats them as zero.
aggregationNoHow to combine values with the same date: 1 AVERAGE (default), 2 COUNT, 3 COUNTA, 4 MAX, 5 MEDIAN, 6 MIN, 7 SUM.

Examples

Example 1 — Next two quarters, with the seasonal dip

Three years of quarterly sales: a steady rise, a peak every third quarter and a dip every fourth.

E2fx=FORECAST.ETS(D2,$B$2:$B$13,$A$2:$A$13)
ABCDEF
1QuarterSalesQuarterFORECAST.ETSFORECAST.LINEAR
219513155165
3211514175170
43135
54105
65115
76135
87155
98125
109135
1110155
1211175
1312145
=FORECAST.ETS(D2,$B$2:$B$13,$A$2:$A$13) Explain

FORECAST.ETS spots the four-quarter cycle: quarter 13 is a first quarter, so it dips to 155. The straight line ignores the season and just continues the average rise.

Example 2 — Monthly dates

With the 1st of each month in A2:A37 and sales in B2:B37, forecast January 2027 with a 12-month season.

=FORECAST.ETS(DATE(2027,1,1),B2:B37,A2:A37,12) Explain

Example 3 — Turn seasonality off

Seasonality 0 forecasts level and trend only.

=FORECAST.ETS(13,B2:B13,A2:A13,0) Explain

Common mistakes

  • #NUM! The target date is not after the end of the timeline, the timeline has no constant step, or more than 30% of the points are missing.
  • #N/A values and timeline are different sizes.
  • Enough history. Use at least two full seasons — 24 months for a yearly pattern in monthly data.

Notes

Kyle Stott

Author

Kyle Stott

Certified Microsoft Excel Expert

Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.