FORECAST.ETS.STAT Function
Returns a statistic about a FORECAST.ETS model — smoothing constants, error measures or the detected step.
Category
Statistical
Available in
Excel 2016+, Microsoft 365
Returns
A number
Overview
FORECAST.ETS.STAT reports on the model behind FORECAST.ETS. statistic_type chooses what you get:
1 Alpha — how quickly the level reacts to new data (0–1). 2 Beta — how quickly the trend reacts. 3 Gamma — how quickly the seasonal pattern updates. 4 MASE — mean absolute scaled error (below 1 beats a naive "same as last period" forecast). 5 SMAPE — symmetric mean absolute percentage error. 6 MAE — mean absolute error. 7 RMSE — root mean squared error. 8 The step size detected in the timeline.
The error measures compare each historical value with the model's one-step-ahead forecast, so lower means the model fits the history better.
Syntax
Arguments
| Argument | Required? | What it does |
|---|---|---|
| values | Yes | The historical values. |
| timeline | Yes | The dates or period numbers, with a constant step. |
| statistic_type | Yes | A number from 1 to 8 (see above). |
| 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 — Check the timeline's step
Orders recorded every seventh day of the year.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Day | Orders | Statistic | Value | |
| 2 | 1 | 52 | Step size | 7 | |
| 3 | 8 | 55 | |||
| 4 | 15 | 51 | |||
| 5 | 22 | 58 | |||
| 6 | 29 | 60 | |||
| 7 | 36 | 57 | |||
| 8 | 43 | 63 | |||
| 9 | 50 | 65 |
Excel found a constant step of 7 — weekly data.
Example 2 — How well does the model fit?
Compare models using an error measure, such as RMSE in the data's own units.
Try it with seasonality 0 as well: a lower RMSE means a better fit to the history.
Common mistakes
- #NUM! statistic_type isn't 1–8, or the timeline problems that also break FORECAST.ETS.
- Use the same settings. Pass the same seasonality, data_completion and aggregation as your FORECAST.ETS formula to describe the same model.
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, FORECAST.ETS.CONFINT, FORECAST.ETS.SEASONALITY.

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.