Returns the length of the repetitive pattern Excel detects for the specified time series. FORECAST.ETS.Seasonality can be used following FORECAST.ETS to identify which automatic seasonality was detected and used in FORECAST.ETS. While it can also be used independently of FORECAST.ETS, the functions are tied since the seasonality detected in this function is identical to the one used by FORECAST.ETS, considering the same input parameters that affect data completion.
Syntax:= FORECAST.ETS.SEASONALITY(values, timeline, [data_completion], [aggregation])
The FORECAST.ETS.SEASONALITY function syntax has the following arguments:
- Values Required. Values are the historical values, for which you want to forecast the next points.
- Timeline Required. The independent array or range of numeric data. The dates in the timeline must have a consistent step between them and can’t be zero. The timeline isn’t required to be sorted, as FORECAST.ETS.SEASONALITY will sort it implicitly for calculations. If a constant step can’t be identified in the provided timeline, FORECAST.ETS.SEASONALITY will return the #NUM! error. If timeline contains duplicate values, FORECAST.ETS.SEASONALITY will return the #VALUE! error. If the ranges of the timeline and values aren’t of same size, FORECAST.ETS.SEASONALITY will return the #N/A error.
- Data completion Optional. Although the timeline requires a constant step between data points, FORECAST.ETS.SEASONALITY supports up to 30% missing data, and will automatically adjust for it. 0 will indicate the algorithm to account for missing points as zeros. The default value of 1 will account for missing points by completing them to be the average of the neighboring points.
-
Aggregation Optional. Although the timeline requires a constant step between data points, FORECAST.ETS.SEASONALITY will aggregate multiple points which have the same time stamp. The aggregation parameter is a numeric value indicating which method will be used to aggregate several values with the same time stamp. The default value of 0 will use AVERAGE, while other options are SUM, COUNT, COUNTA, MIN, MAX, MEDIAN.
Example: Let’s look at some Excel FORECAST.ETS.SEASONALITY function examples and explore how to use the FORECAST.ETS.SEASONALITY function as a worksheet function in Microsoft Excel:
The Excel Forecast.Ets.Seasonality function can be used to forecast length of the seasonal pattern in earnings between Jan-2018 and Apr-2020, as follows:
Syntax: =FORECAST.ETS.SEASONALITY(C2:C29,B2:B29)
Result:
The spreadsheet on the right shows a set of monthly earnings between Jan-2018 and Apr-2020. These values are plotted in the chart below:
Note:
- 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.
