Home » ExcelBuiltInFunctions » ExcelStatisticalFunctions » ExcelForecastEtsSeasonalityFunction
The Excel Forecast.Ets.Seasonality Function
Related Function:
Forecast.EtsFunction Description
The Excel Forecast.Ets.Seasonality function calculates the length of a repetitive pattern in a timeline.
The syntax of the function is:
FORECAST.ETS.SEASONALITY( values, timeline, [data completion], [aggregation] )
Where the function arguments are:
values    The array of historical known values. 
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.

[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 

0  Treat 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  2  Count  3  Counta  4  Max  5  Median  6  Min  7  Sum 

Notes:
 The pattern calculated by the Forecast.Ets.Seasonality function is the same pattern that is calculated automatically by the Forecast.Ets function).
 If the Forecast.Ets.Seasonality function requires a reasonable number of data values to identify a seasonal pattern. Greater numbers of values will result in greater accuracy in the result.
 If the Forecast.Ets.Seasonality function is unable to detect a repetitive pattern, it returns the value 0.
 The Forecast.Ets.Seasonality 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.Seasonality Function Example
 A  B 

1  Month  Earnings 

2  Jan2015  879 

3  Feb2015  1259 

4  Mar2015  1230 

5  Apr2015  1471 

6  May2015  1638 

7  Jun2015  1371 

8  Jul2015  1562 

9  Aug2015  1526 

10  Sep2015  1125 

11  Oct2015  1340 

12  Nov2015  830 

13  Dec2015  726 

14  Jan2016  917 

15  Feb2016  1028 

16  Mar2016  1449 

17  Apr2016  1225 

18  May2016  1437 

19  Jun2016  1637 

20  Jul2016  1345 

21  Aug2016  1148 

22  Sep2016  1075 

23  Oct2016  928 

24  Nov2016  852 

25  Dec2016  752 

26  Jan2017  989 

27  Feb2017  1304 

28  Mar2017  1192 

29  Apr2017  1265 

The above spreadsheet on the right shows a set of monthly earnings between Jan2015 and Apr2017. These values are plotted in the chart below:
The Excel Forecast.Ets.Seasonality function can be used to forecast length of the seasonal pattern in earnings between Jan2015 and Apr2017, as follows:
=FORECAST.ETS.SEASONALITY( B2:B29, A2:A29 )
This gives the result 11.
Note that, in the above example:
 The function has returned the a seasonality value of 11 for the data supplied (although we might have expected the seasonal pattern to be 12 for this data). The calculated seasonality value should have greater accuracy as more data values are supplied to the function.
 The [data completion] and [aggregation] arguments have been omitted from the function and so use the default values. (I.e. [data completion] = 1 and [aggregation] = 0).
For further details of the Excel Forecast.Ets.Seasonality function, see the Microsoft Office website
Forecast.Ets.Seasonality Function Errors
If you get an error from the Forecast.Ets.Seasonality 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 [data completion] is not equal to 0 or 1;
 The supplied [aggregation] value is not within the valid range 1  7.

#VALUE!    Occurs if either of the supplied [data completion] or the [aggregation] arguments is nonnumeric. 