The FORECAST.ETS.STAT function is a Statistical function that returns a statistical information based on time series forecasting. This function can be used with FORECAST.ETS function to see the statistical values of forecasting. In this guide, we’re going to show you how to use the FORECAST ETS STAT function and go over some tips and error handling methods.

Supported Versions

  • Excel 2016 or newer
  • Not supported Excel for the Web, iOS, or Android.

FORECAST ETS STAT Function Syntax

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

Arguments

values The historical values in data points. (y values)
timeline The historical data points (dates). (x values)
statistic_type An integer value between 1 and 8, determines the statistic you want to get.
[seasonality]

Optional. A positive numeric value represents the periods in a season.

  • 0: No seasonality, linear prediction.
  • 1: Automatic. Default value.
[data_completion]

Optional. This determines how Excel treats missing data.

  • 0: Treat as zero (0).
  • 1: The average of the neighboring points. Default values.
[aggregation]

Optional. The aggregation method for the values with the same timestamp.

  • 1: AVERAGE (Default)
  • 2: COUNT
  • 3: COUNTA
  • 4: MAX
  • 5: MEDIAN
  • 6: MIN
  • 7: SUM

Statistic Type

statistic_type Statistic Name Description
1 Alpha parameter of ETS algorithm Returns the base value parameter—a higher value gives more weight to recent data points.
2 Beta parameter of ETS algorithm Returns the trend value parameter—a higher value gives more weight to the recent trend.
3 Gamma parameter of ETS algorithm Returns the seasonality value parameter—a higher value gives more weight to the recent seasonal period.
4 MASE metric Returns the mean absolute scaled error metric—a measure of the accuracy of forecasts.
5 SMAPE metric Returns the symmetric mean absolute percentage error metric—an accuracy measure based on percentage errors.
6 MAE metric Returns the mean absolute percentage error metric—an accuracy measure based on percentage errors.
7 RMSE metric Returns the root mean squared error metric—a measure of the differences between predicted and observed values.
8 Step size detected Returns the step size detected in the historical timeline.

FORECAST.ETS.STAT Function Examples

Default form

The FORECAST.ETS.STAT function needs a date for the values and data points (values, timeline) you are using to forecast as well as the statistic type. The remaining three arguments are optional.

=FORECAST.ETS.STAT(Historical_Values,Historical_Dates,Statistic_Type)

  • values: C5:C47 (Historical_Values)
  • timeline: B5:B47 (Historical_Dates)
  • statistic_type: G10 ([@[Statistic Type]])

Excel FORECAST ETS STAT Function

You can see the other stats in the same table.

Optional arguments

Using optional arguments is related to the dataset and the data origin. If you are in doubt, do not hesitate to try a different combination to find proper options for your data. Here is an example of how different options can alter the statistical values as well as predicted values.

Download Workbook

Remarks

  • The date values in the timeline range aren't required to be sorted. However, if a constant step can't be identified in the provided timeline, the function returns #NUM!
  • If the timeline range contains duplicate values, FORECAST.ETS.STAT will return the #VALUE! error.
  • If the ranges of the timeline and values aren't of the same size, the function will return the #N/A
  • The maximum value for the seasonality is 8760 (number of hours in a year). Any number above will return #NUM!

Excel Forecasting Functions

Forecasting with linear regression FORECAST.LINEAR
Forecasting using Exponential Triple Smoothing FORECAST.ETS
Seasonality in forecasting using Exponential Triple Smoothing FORECAST.ETS.SEASONALITY
Confidence interval of a forecasting FORECAST.ETS.CONFINT