Home »
ExcelBuiltInFunctions »
ExcelStatisticalFunctions »
ExcelForecastEtsSeasonalityFunction
The Excel Forecast.Ets.Seasonality Function
Related Function:
Forecast.Ets
Function 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.
