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
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
Arguments
| Argument | Required? | What it does |
|---|---|---|
| target_date | Yes | The date or period to forecast; must come after the last point of the timeline. |
| values | Yes | The historical values. |
| timeline | Yes | The dates or period numbers for the values, with a constant step. |
| seasonality | No | 1 (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_completion | No | 1 (default) fills missing points with the average of their neighbours; 0 treats them as zero. |
| aggregation | No | How 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.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Quarter | Sales | Quarter | FORECAST.ETS | FORECAST.LINEAR | |
| 2 | 1 | 95 | 13 | 155 | 165 | |
| 3 | 2 | 115 | 14 | 175 | 170 | |
| 4 | 3 | 135 | ||||
| 5 | 4 | 105 | ||||
| 6 | 5 | 115 | ||||
| 7 | 6 | 135 | ||||
| 8 | 7 | 155 | ||||
| 9 | 8 | 125 | ||||
| 10 | 9 | 135 | ||||
| 11 | 10 | 155 | ||||
| 12 | 11 | 175 | ||||
| 13 | 12 | 145 |
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.
Example 3 — Turn seasonality off
Seasonality 0 forecasts level and trend only.
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
- Excel tunes its smoothing constants with its own optimiser, so figures from desktop Excel can differ slightly from the Sandbox on noisy data.
- Related functions: FORECAST.ETS.CONFINT, FORECAST.ETS.SEASONALITY, FORECAST.ETS.STAT, FORECAST.LINEAR.

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.