The FORECAST.ETS.SEASONALITY function in Excel is designed to determine the length of a seasonality cycle in a given time series dataset. This powerful feature helps users analyze patterns over time, making it easier to create predictions based on historical data.

Syntax

FORECAST.ETS.SEASONALITY(target_date, values, timeline, [seasonality], [data_completion], [aggregation])

• target_date: The date for which you want to estimate the seasonality.
• values: The numeric data points for which you want to find the seasonality.
• timeline: The dates corresponding to the values provided.
• seasonality (optional): A numerical value specifying the seasonality cycle (1 for automatic detection, or a positive integer).
• data_completion (optional): Enables handling of missing values (TRUE or FALSE).
• aggregation (optional): The method to aggregate multiple values (0 for averaging, 1 for summing, etc.).

Example #1

FORECAST.ETS.SEASONALITY("2023-12-01", A2:A10, B2:B10)

In this example, Excel will calculate the length of the seasonal pattern in the data from the range B2:B10, based on the corresponding timeline in A2:A10. If the output is ’12’, it indicates a yearly seasonality effect.

Example #2

FORECAST.ETS.SEASONALITY("2023-06-01", B2:B10, A2:A10, 1, FALSE, 0)

Here, the function looks at the timeline in A2:A10 and the values in B2:B10, automatically detecting the seasonality without accounting for missing values. A result of ‘6’ would suggest a half-year cycle in the data.

Example #3

FORECAST.ETS.SEASONALITY("2023-07-01", B2:B10, A2:A10, 12, TRUE, 1)

In this case, the function is instructed to assume a yearly seasonal rhythm and accommodate missing data by aggregating with summation. If the result is ’12’, this confirms the intended seasonal length according to the user’s specification.

Error handling

• VALUE!: This error occurs if any of the input parameters are of the wrong data type. Ensure your dates and numeric values are correctly formatted.
• N/A: This indicates that the function was unable to find any data to generate a forecast, commonly seen with insufficient historical values.
• NUM!: It appears when the parameters lead to an infeasible calculation, such as incorrect seasonality values. Verify the inputs and adjust accordingly.
• REF!: This error is shown if the timeline or values range refers to deleted or invalid cells. Check your ranges to make sure they are accurate.

Conclusion

The FORECAST.ETS.SEASONALITY function is a vital tool for analysts who want to grasp underlying patterns in their time series data. By identifying the cyclic nature of data, users can enhance their forecasting accuracy and make well-informed business decisions. Understanding its syntax and potential error messages is essential for leveraging its full capabilities effectively.