The FORECAST.ETS.STAT function in Excel is a powerful tool designed for time series forecasting. It provides various statistical measures based on seasonal patterns and trends found in your historical data, allowing users to make informed predictions about future values. By leveraging the Exponential Triple Smoothing (ETS) algorithm, this function is ideal for users looking to conduct detailed analyses in finance, sales, and other domains where forecasting plays a pivotal role.

Syntax

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

• target_date: The date for which you want to predict the value.
• values: The historical data points you want to analyze for forecasting.
• timeline: The set of dates corresponding to each data point in values.
• seasonality: (Optional) Indicates the length of the seasonal pattern; use 1 for no seasonality.
• data_completion: (Optional) A logical value that specifies whether to fill in missing data. TRUE fills missing points, while FALSE skips them.
• aggregation: (Optional) Determines how to aggregate values if multiple points exist for a specific date; the default is an average.

Example #1

FORECAST.ETS.STAT("2023-10-01", A2:A10, B2:B10)

In this example, the function predicts the value for the date “2023-10-01” based on the historical data in range A2:A10 (timeline) and B2:B10 (values). If A2:A10 contains dates and B2:B10 contains corresponding values, you might get a result of 150.

Example #2

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

Here, the function forecasts the value for “2023-10-01” with a seasonality of 12, suggesting an annual cycle, and fills any missing data. Suppose it returns a value of 180.

Example #3

FORECAST.ETS.STAT("2023-10-01", A2:A10, B2:B10, 0, FALSE, 2)

In this call, the function predicts the same date without seasonality and skips any missing data points, potentially resulting in a value of 160.

Error handling

• VALUE! – One of the parameters is of the wrong type or not well-formed (e.g., non-date or non-numeric).
• N/A – The timeline and values of historical data don’t match, or there’s insufficient historical data available for forecasting.
• NUM! – The parameters result in invalid calculations, such as negative values for seasonality or aggregation types that are not recognized.

Conclusion

The FORECAST.ETS.STAT function is a valuable asset for anyone looking to perform detailed statistical analyses in Excel. With its ability to produce various statistical outputs based on time series data, it empowers users to make informed decisions with confidence. Understanding its parameters and error handling can significantly enhance the accuracy and effectiveness of your forecasting efforts.