The `FORECAST.ETS.SEASONALITY` function in Excel is used to identify the length of the repeating pattern (seasonality) in a time series dataset. It is part of the suite of functions related to Exponential Smoothing, which Excel uses to make forecasts by taking past data with seasonality into account. This can be particularly useful for financial data, sales data, inventory data, or any dataset where you expect seasonal trends.
Here’s how you can use the `FORECAST.ETS.SEASONALITY` function:
Syntax
FORECAST.ETS.SEASONALITY(target_date, values, timeline, [data_completion], [aggregation])
Parameters
- target_date: This is the data point for which you want to predict a value. The date can be specified as a date value, a date serial number, or a text reference to a date.
- values: This range contains the historical values for which you want to forecast. It must be a numeric series.
- timeline: A range with a date/time series that corresponds to the `values` series. The spacing in this timeline should be consistent. It should have the same length as the `values`.
- [data_completion] (optional): This is a numerical value (0 or 1) that indicates how to handle missing data within your timeline.
- `0`: It treats missing data by default.
- `1`: It avoids missing points by using completion of averages.
- [aggregation] (optional): This parameter is used to aggregate several values equal to the same time stamp. A numeric value between 1 and 7 can be used to determine what function should be used for aggregation:
- `1`: AVERAGE (default)
- `2`: COUNT
- `3`: COUNTA
- `4`: MAX
- `5`: MEDIAN
- `6`: MIN
- `7`: SUM
Example
Supposing you have historical sales data with a clear seasonal pattern that repeats every year. Your `values` are in range B2:B13 (sales amounts for each month), and your `timeline` is in A2:A13 (corresponding dates, such as Jan-2020 to Dec-2020).
You want to find out the seasonality for this dataset:
=FORECAST.ETS.SEASONALITY(A2, B2:B13, A2:A13)
Notes
- Consistency: Make sure that the timeline has a consistent interval (e.g., daily, weekly, monthly) and that it matches the `values` range.
- Seasonality Range: The function will analyze the dataset and detect the repetition interval.
- Missing Data: If there are missing data points, you may specify how they should be handled using the `data_completion` parameter.
- Dynamic Changes: Since seasonality is auto-detected by this function, updates in the dataset can change the predicted seasonality.
Summary
The `FORECAST.ETS.SEASONALITY` function is a powerful tool within Excel for analyzing time series data that exhibits regular patterns. By understanding and using this function, you can gain insights into the cyclic behavior of a dataset and make better forecasts.
Unlock Your Potential
Excel
Access
Power BI
