The Excel Forecast.Ets.Seasonality Function

Related Function:

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


Excel Forecast.Ets.Seasonality Function Example

  A B
1 Month Earnings
2 Jan-2015 879
3 Feb-2015 1259
4 Mar-2015 1230
5 Apr-2015 1471
6 May-2015 1638
7 Jun-2015 1371
8 Jul-2015 1562
9 Aug-2015 1526
10 Sep-2015 1125
11 Oct-2015 1340
12 Nov-2015 830
13 Dec-2015 726
14 Jan-2016 917
15 Feb-2016 1028
16 Mar-2016 1449
17 Apr-2016 1225
18 May-2016 1437
19 Jun-2016 1637
20 Jul-2016 1345
21 Aug-2016 1148
22 Sep-2016 1075
23 Oct-2016 928
24 Nov-2016 852
25 Dec-2016 752
26 Jan-2017 989
27 Feb-2017 1304
28 Mar-2017 1192
29 Apr-2017 1265

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.Seasonality Function Example

The Excel Forecast.Ets.Seasonality function can be used to forecast length of the seasonal pattern in earnings between Jan-2015 and Apr-2017, as follows:


This gives the result 11.

Note that, in the above example:

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 non-numeric.