The Excel Forecast.Ets.Stat Function

Related Function:
Forecast.Ets

Function Description

The Excel Forecast.Ets.Stat function calculates a specified statistical value, relating to a time series forecasting.

The syntax of the function is:

FORECAST.ETS.STAT( values, timeline, statistic_type, [seasonality], [data completion], [aggregation] )

where the function arguments are:

values-The array of historical known values corresponding to an array of dates/times.
timeline-

The independent array of dates/times, corresponding to each of the values.

This must satisfy the following:

  • The timeline array must have the same length as the values array;
  • The dates/times in the timeline must have a consistent step length between them, although:
    • Up to 30% of points may be missing and dealt with, according to the value of the [data completion] argument.
    • There may be duplicates in the timeline, whose corresponding values will be aggregated, as defined by the [aggregation] argument.
  • The dates/times in the timeline can be in any order.
statistic_type-

A numeric value, indicating which statistical value is to be returned.

This must be an integer between 1 and 8, meaning:

statistic_typeStatistic Returned
1Alpha parameter of ETS algorithm
2Beta parameter of ETS algorithm
3Gamma parameter of ETS algorithm
4MASE metric
5SMAPE metric
6MAE metric
7RMSE metric
8Step size detected
[seasonality]-

An optional argument indicating the algorithm that should be used to detect seasonality in the data.

If supplied, this must be a positive integer between 0 and 8784, meaning:

[seasonality]Algorithm
0No seasonality (i.e. use the linear algorithm for the forecast).
1 (or omitted)Automatically calculate the seasonality and use positive, whole numbers for the length of the seasonal pattern.
integer ≥ 2
& ≤ 8784
Use patterns of this length as the seasonality.
[data completion]-

An optional argument specifying how the algorithm should handle missing points in the timeline.

If supplied, the [data completion] argument can have the value 0 or 1 meaning:

[data completion]Algorithm
0Treat missing points as having the value zero.
1 (or omitted)Calculate the value for missing points to be the average of the neighbouring values.
[aggregation]-

An optional argument specifying how the algorithm should aggregate values that have the same timestamp.

If supplied, this can be any integer between 1 and 7 meaning:

[aggregation]Aggregation Method
1 (or omitted)Average
2Count
3Counta
4Max
5Median
6Min
7Sum

Note: The Forecast.Ets.Stat function was first introduced in Excel 2016, so is not available in earlier versions of Excel. It is also not available in Excel 2016 for Mac.


Excel Forecast.Ets.Stat Function Example

 AB
1MonthEarnings
2Jan-2015879
3Feb-20151259
4Mar-20151230
5Apr-20151471
6May-20151638
7Jun-20151371
8Jul-20151562
9Aug-20151526
10Sep-20151125
11Oct-20151340
12Nov-2015830
13Dec-2015726
14Jan-2016917
15Feb-20161028
16Mar-20161449
17Apr-20161225
18May-20161437
19Jun-20161637
20Jul-20161345
21Aug-20161148
22Sep-20161075
23Oct-2016928
24Nov-2016852
25Dec-2016752
26Jan-2017989
27Feb-20171304
28Mar-20171192
29Apr-20171265

The above spreadsheet on the right shows a set of monthly earnings between Jan-2015 and Apr-2017. These values are plotted in the chart below:

Chart Showing Earnings Data for Forecast.Ets.Stat Function Example

The Forecast.Ets function has previously calculated the forecast earnings for May 2017 to be 1461.632054.

The Excel Forecast.Ets.Stat function can be used to return statistical information relating to this forecast. For example, in order to return the Alpha parameter of the ETS algorithm:

=FORECAST.ETS.STAT( B2:B29, A2:A29, 1 )

which gives the result 0.126.

If you want to return the step size detected by the ETS algorithm:

=FORECAST.ETS.STAT( B2:B29, A2:A29, 8 )

which returns the value 31.

For further details of the Excel Forecast.Ets.Stat function, see the Microsoft Office website


Forecast.Ets.Stat Function Errors

If you get an error from the Forecast.Ets.Stat function, this is likely to be one of the following:

Common Errors
#N/A-Occurs if the supplied values and timeline arrays have different lengths.
#NUM!-

Occurs if either:

  • A consistent step size cannot be identified in the dates/times of the supplied timeline;
  • The supplied statistic_type value is not within the valid range 1 - 8;
  • The supplied [seasonality] value is not within the valid range 0 - 8784;
  • The supplied [data completion] is not equal to 0 or 1;
  • The supplied [aggregation] value is not within the valid range 1 - 7.
#VALUE!-Occurs if one or more of the supplied statistic_type, [seasonality], [data completion] or [aggregation] arguments is non-numeric.