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

Want to practice FORECAST.ETS.STAT in a real spreadsheet? Open a blank sheet in our Practice Sandbox and try it with live data, with the AI Excel tutor beside you. No Excel needed — just a free account.Try it free in the Sandbox

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

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

Arguments

ArgumentRequired?What it does
valuesYesThe historical values.
timelineYesThe dates or period numbers, with a constant step.
statistic_typeYesA number from 1 to 8 (see above).
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 — Check the timeline's step

Orders recorded every seventh day of the year.

E2fx=FORECAST.ETS.STAT(B2:B9,A2:A9,8)
ABCDE
1DayOrdersStatisticValue
2152Step size7
3855
41551
52258
62960
73657
84363
95065
=FORECAST.ETS.STAT(B2:B9,A2:A9,8) Explain

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.

=FORECAST.ETS.STAT(B2:B13,A2:A13,7) Explain

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

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.