The Excel FORECAST.ETS function predicts a future value on a timeline using exponential smoothing (the ETS algorithm), automatically detecting seasonality to project the next period of a time series.
Syntax
| Argument | Description | |
|---|---|---|
target_date | Required | The point on the timeline for which you want a predicted value — usually the next date after your known data. |
values | Required | The range of historical values to forecast forward (the y data). |
timeline | Required | The range of dates or numbers for the historical values. Points should be evenly spaced; a constant step is required. |
seasonality | Optional | 0 = no seasonality, 1 = detect automatically (default), or a positive number specifying the season length. |
data_completion | Optional | How to handle missing points: 1 (default) interpolates, 0 treats gaps as zeros. |
aggregation | Optional | How to combine duplicate timeline points (1 = AVERAGE by default). |
How to use it
FORECAST.ETS uses the AAA (additive) version of the Exponential Triple Smoothing algorithm. Unlike linear FORECAST, it learns level, trend, and seasonal patterns from a real timeline and projects them forward.
Because it needs a genuine spilled timeline and a matching values range, FORECAST.ETS is shown here with cell references rather than a single self-contained formula. Lay your dates in A2:A19 (evenly spaced — e.g. one per month), the matching values in B2:B19, and put the next date in A20; the formula returns the predicted value for that date.
The companion functions extend it: FORECAST.ETS.CONFINT returns a confidence interval, FORECAST.ETS.SEASONALITY reports the detected season length, and FORECAST.ETS.STAT exposes the smoothing statistics.
Tip: The timeline must use a constant step. If your dates have small irregularities, Excel still requires a consistent interval — clean the series first or let data_completion interpolate missing points.
Try it: interactive demo
With 18 monthly dates in A2:A19 and sales in B2:B19, and the next month's date in A20:
Practice workbook
Frequently asked questions
How is FORECAST.ETS different from FORECAST?
FORECAST (and FORECAST.LINEAR) fits one straight line, ideal for a steady trend. FORECAST.ETS uses exponential smoothing over a timeline and can capture seasonality and a changing trend, making it better for periodic time-series data.What does the seasonality argument do?
1 (default) auto-detects the season length, 0 turns seasonality off, and a positive number forces a specific period (e.g. 12 for monthly data with a yearly cycle).Why does FORECAST.ETS need an evenly spaced timeline?
data_completion can interpolate them.Which Excel versions have FORECAST.ETS?
Master functions like this in one day
This page covers one function. Our Excel Formulas and Functions class covers the 30 that matter most — live, hands-on, taught by professionals in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class