The `FORECAST.ETS.STAT` function in Excel is used to return a specific statistic related to the forecast generated by the Exponential Smoothing (ETS) algorithm, which is commonly used for time series forecasting. Here’s how you can use this function:
Syntax
FORECAST.ETS.STAT(target_date, values, timeline, statistic_type[, seasonality, data_completion, aggregation])
Parameters
- `target_date`: The date for which you want to predict the value. This date should align with your timeline range.
- `values`: The historical values for which you want to forecast the statistics. This is your known data that corresponds to the timeline.
- `timeline`: The range that represents the timeline or dates associated with your historical data. This range should have a constant step between data points.
- `statistic_type`: The type of statistic you want to return. It is an integer value representing different statistics:
- 1: Alpha
- 2: Beta
- 3: Gamma
- 4: MSE (Mean Squared Error)
- 5: SMAPE (Symmetric Mean Absolute Percent Error)
- 6: MAE (Mean Absolute Error)
- 7: RMSE (Root Mean Squared Error)
- 8: Step size
- 9: Returned seasonality
- 10: Regressor count
- 11: R-squared
- 12: Forecast error
- `seasonality` (optional): A numerical value indicating the length of the seasonal pattern. By default, Excel will detect it automatically. If you do not want a seasonal pattern, set this to 0.
- `data_completion` (optional): This parameter specifies how to treat missing data:
- 0: Missing data is treated as zeros.
- 1: Missing data is averaged from neighboring points.
- Default is 1.
- `aggregation` (optional): Specifies how to aggregate data if you have duplicate timestamps:
- 1: AVERAGE
- 2: COUNT
- 3: COUNTA
- 4: MAX
- 5: MEDIAN
- 6: MIN
- 7: SUM
- Default is AVERAGE.
Example
Suppose you have a dataset with dates in column A (timeline) and sales in column B (values), and you want to calculate the Mean Squared Error (MSE) statistic for your ETS forecast. You would write:
=FORECAST.ETS.STAT(A2, B2:B50, A2:A50, 4)
Things to Keep in Mind
- Make sure your timeline has consistent intervals; otherwise, you might encounter errors or inaccurate forecasts.
- The data completion and aggregation parameters help in managing missing and redundant data, which can improve forecast accuracy.
Using `FORECAST.ETS.STAT`, you can analyze and refine your forecast by understanding key metrics like seasonality, trend, error margins, and more, which are vital for making informed business decisions.
Unlock Your Potential
Excel
Access
Power BI
