Purpose
Return value
Syntax
=FORECAST.ETS.SEASONALITY(values,timeline,[data_completion],[aggregation])- values - Existing or historical values (y values).
- timeline - Numeric timeline values (x values).
- data_completion - [optional] Missing data treatment (0 = treat as zero, 1 = average). Default is 1.
- aggregation - [optional] Aggregation behavior. Default is 1 (AVERAGE). See other options below.
How to use
The FORECAST.ETS.SEASONALITY function returns the length in time of a seasonal pattern based on existing values and a timeline. FORECAST.ETS.SEASONALITY can be used to calculate the season length for numeric values like sales, inventory, expenses, etc. exhibit a seasonal pattern. If a pattern cannot be detected, FORECAST.ETS.SEASONALITY returns zero.
Example
In the example shown, the formula in cell H16 is:
=FORECAST.ETS.SEASONALITY(C5:C16,B5:B16)
where C5:C16 contains existing values, and B5:B16 contains a timeline. With these inputs, the FORECAST.ETS.SEASONALITY function returns 4. The result is 4 because the values in C5:C16 represent quarterly sales data, and the length of the season is 1 year, which is 4 quarters.
The chart to the right shows this data plotted in a scatter plot.
Argument notes
The values argument contains the dependent array or range of data, also called y values. These are existing historical values from which a season length will be calculated.
The timeline argument is the independent array or range of values, also called x values. The timeline, must consist of numeric values with a constant step interval. For example, the timeline could be yearly, quarterly, monthly, daily, etc. The timeline can also be a simple list of numeric periods, as in the example shown.
The data_completion argument is optional and specifies how FORECAST.ETS.SEASONALITY should handle missing data points. The options are 1 (default) and zero. By default, FORECAST.ETS.SEASONALITY will provide missing data points by averaging neighboring data points. If zero is provided for data_completion, FORECAST.ETS will treat missing data points as zero.
The aggregation argument is optional, and controls how the function should aggregate data points when the timeline contains duplicate timestamps. The default is 1, which specifies AVERAGE. Other options are given in the table below.
Note: It is better to perform aggregation before using FORECAST.ETS.SEASONALITY to make results as accurate as possible.
| Value | Behavior |
|---|---|
| 1 (or omitted) | AVERAGE |
| 2 | COUNT |
| 3 | COUNTA |
| 4 | MAX |
| 5 | MEDIAN |
| 6 | MIN |
| 7 | SUM |
Errors
The FORECAST.ETS.SEASONALITY function will return errors as shown below.
| Error | Cause |
|---|---|
| #VALUE! |
|
| #N/A |
|
| #NUM |
|
Related functions
FORECAST Function
The Excel FORECAST function predicts a value based on existing values along a linear trend. FORECAST calculates future value predictions using linear regression, and can be used to predict numeric values like sales, inventory, expenses, measurements, etc.
Note: Starting with Excel...
FORECAST.LINEAR Function
The FORECAST.LINEAR function predicts a value based on existing values along a linear trend. FORECAST.LINEAR calculates future value predictions using linear regression, and can be used to predict numeric values like sales, inventory, test scores, expenses, measurements, etc.
...
FORECAST.ETS Function
The Excel FORECAST.ETS function predicts a value based on existing values that follow a seasonal trend. FORECAST.ETS can be used to predict numeric values like sales, inventory, expenses, etc. with a seasonal pattern.
FORECAST.ETS.CONFINT Function
The Excel FORECAST.ETS.CONFINT function returns a confidence interval for a forecast value at a specific point on a timeline. It is designed to be used along with the FORECAST.ETS function as a way to show forecast accuracy.
FORECAST.ETS.SEASONALITY Function
The Excel FORECAST.ETS.SEASONALITY function returns the length in time of a seasonal pattern based on existing values and a timeline.
FORECAST.ETS.STAT Function
The Excel FORECAST.ETS.STAT function returns a particular statistical value relating related to time series forecasting with the FORECAST.ETS function. The statistic_type argument determines which statistic is returned by FORECAST.ETS.STAT...
