FORECAST.ETS.STAT function
FORECAST.ETS.STAT returns one chosen statistic, such as a parameter or error measure, from the fitted FORECAST.ETS model. It lets you judge how well the model matches your history.
Example
Excel shows 0.751
AE
AF
1
=DATE(2023,1,1)
=100+10*MOD(1-1,4)+1
2
=DATE(2023,2,1)
=100+10*MOD(2-1,4)+2
3
=DATE(2023,3,1)
=100+10*MOD(3-1,4)+3
4
=DATE(2023,4,1)
=100+10*MOD(4-1,4)+4
5
=DATE(2023,5,1)
=100+10*MOD(5-1,4)+5
6
=DATE(2023,6,1)
=100+10*MOD(6-1,4)+6
7
=DATE(2023,7,1)
=100+10*MOD(7-1,4)+7
8
=DATE(2023,8,1)
=100+10*MOD(8-1,4)+8
9
=DATE(2023,9,1)
=100+10*MOD(9-1,4)+9
10
=DATE(2023,10,1)
=100+10*MOD(10-1,4)+10
11
=DATE(2023,11,1)
=100+10*MOD(11-1,4)+11
12
=DATE(2023,12,1)
=100+10*MOD(12-1,4)+12
13
=DATE(2023,13,1)
=100+10*MOD(13-1,4)+13
14
=DATE(2023,14,1)
=100+10*MOD(14-1,4)+14
15
=DATE(2023,15,1)
=100+10*MOD(15-1,4)+15
16
=DATE(2023,16,1)
=100+10*MOD(16-1,4)+16
17
=DATE(2023,17,1)
=100+10*MOD(17-1,4)+17
18
=DATE(2023,18,1)
=100+10*MOD(18-1,4)+18
19
=DATE(2023,19,1)
=100+10*MOD(19-1,4)+19
20
=DATE(2023,20,1)
=100+10*MOD(20-1,4)+20
21
=DATE(2023,21,1)
=100+10*MOD(21-1,4)+21
22
=DATE(2023,22,1)
=100+10*MOD(22-1,4)+22
23
=DATE(2023,23,1)
=100+10*MOD(23-1,4)+23
24
=DATE(2023,24,1)
=100+10*MOD(24-1,4)+24
Data
Syntax
FORECAST.ETS.STAT(values, timeline, statistic_type, [seasonality], [data_completion], [aggregation])
Arguments in [square brackets] are optional.
-
values(required) - Historical values.
-
timeline(required) - Dates or numbers with a constant step.
-
statistic_type(required) - Number 1 to 8 choosing which statistic (alpha, beta, gamma, MASE, SMAPE, MAE, RMSE, step size).
-
seasonality(optional) - 1 auto, 0 none, or season length.
-
data_completion(optional) - 1 interpolate (default), 0 zeros.
-
aggregation(optional) - How to merge duplicate timestamps.
Returns: One chosen statistic describing the fitted smoothing model.
What Excel actually returns
Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.
| Formula | Excel shows | Case |
|---|---|---|
=FORECAST.ETS.STAT(Data!AF1:AF24,Data!AE1:AE24,1) | 0.751 | statistic_type 1 |
=FORECAST.ETS.STAT(Data!AF1:AF24,Data!AE1:AE24,2) | 0.001 | statistic_type 2 |
=FORECAST.ETS.STAT(Data!AF1:AF24,Data!AE1:AE24,3) | 0.001 | statistic_type 3 |
=FORECAST.ETS.STAT(Data!AF1:AF24,Data!AE1:AE24,4) | 0 | statistic_type 4 |
=FORECAST.ETS.STAT(Data!AF1:AF24,Data!AE1:AE24,5) | 0 | statistic_type 5 |
=FORECAST.ETS.STAT(Data!AF1:AF24,Data!AE1:AE24,6) | 0 | statistic_type 6 |
=FORECAST.ETS.STAT(Data!AF1:AF24,Data!AE1:AE24,9) | #NUM! | statistic_type 9 |
=FORECAST.ETS.STAT(Data!AF1:AF24,Data!AE1:AE24,0) | #NUM! | statistic_type 0 |
The Data sheet these formulas read
A cell that starts with = holds that formula.
AE
AF
1
=DATE(2023,1,1)
=100+10*MOD(1-1,4)+1
2
=DATE(2023,2,1)
=100+10*MOD(2-1,4)+2
3
=DATE(2023,3,1)
=100+10*MOD(3-1,4)+3
4
=DATE(2023,4,1)
=100+10*MOD(4-1,4)+4
5
=DATE(2023,5,1)
=100+10*MOD(5-1,4)+5
6
=DATE(2023,6,1)
=100+10*MOD(6-1,4)+6
7
=DATE(2023,7,1)
=100+10*MOD(7-1,4)+7
8
=DATE(2023,8,1)
=100+10*MOD(8-1,4)+8
9
=DATE(2023,9,1)
=100+10*MOD(9-1,4)+9
10
=DATE(2023,10,1)
=100+10*MOD(10-1,4)+10
11
=DATE(2023,11,1)
=100+10*MOD(11-1,4)+11
12
=DATE(2023,12,1)
=100+10*MOD(12-1,4)+12
13
=DATE(2023,13,1)
=100+10*MOD(13-1,4)+13
14
=DATE(2023,14,1)
=100+10*MOD(14-1,4)+14
15
=DATE(2023,15,1)
=100+10*MOD(15-1,4)+15
16
=DATE(2023,16,1)
=100+10*MOD(16-1,4)+16
17
=DATE(2023,17,1)
=100+10*MOD(17-1,4)+17
18
=DATE(2023,18,1)
=100+10*MOD(18-1,4)+18
19
=DATE(2023,19,1)
=100+10*MOD(19-1,4)+19
20
=DATE(2023,20,1)
=100+10*MOD(20-1,4)+20
21
=DATE(2023,21,1)
=100+10*MOD(21-1,4)+21
22
=DATE(2023,22,1)
=100+10*MOD(22-1,4)+22
23
=DATE(2023,23,1)
=100+10*MOD(23-1,4)+23
24
=DATE(2023,24,1)
=100+10*MOD(24-1,4)+24
Data
Watch out for
- statistic_type must be an integer from 1 to 8.
- Specialist output; most users only need the forecast itself.
Where it works
- Excel
- FORECAST.ETS.STAT arrived in Excel 2016. Older versions do not have it.
- Google Sheets
- No direct equivalent. No Sheets counterpart.
Part of Statistical functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .