The FORECAST.ETS.STAT function is (optionally) used to output forecast statistics as part of the “Forecast sheet” feature in Excel. These are statistics relevant to the forecast created by the feature, which relies on the FORECAST.ETS function. In the example shown above, the FORECAST.ETS.STAT function has been inserted manually to output all eight available forecast statistics based on the historical data and timeline shown. The statistic_type values come from column F.
Statistical values
The statistical value to return is determined by the statistic_type argument. The table below shows the eight possible values and corresponding results.
Argument notes
The values argument contains the dependent array or range of data, also called y values. These are existing historical values from which a prediction will be calculated. The timeline argument is the independent array or range of values, also called x values. The timeline must consist of numeric values with a constant step interval. For example, the timeline could be yearly, quarterly, monthly, daily, etc. The timeline can also be a simple list of numeric periods. It is not required that the timeline be sorted. The seasonality argument is optional and represents the length of the seasonal pattern expressed in timeline units. For example, in the example shown, data is quarterly, so seasonality could be given as 4, since there are 4 quarters in a year, and the seasonal pattern is 1 year. Allowed values are 0 (no seasonality, use linear algorithm), 1 (calculate seasonal pattern automatically), and n (manual season length, a number between 2 and 8784, inclusive). The number 8784 = 366 x 24, the number of hours in a leap year. The data_completion argument is optional and specifies how FORECAST.ETS should handle missing data points. The options are 1 (default) and zero. By default, FORECAST.ETS will provide missing data points by averaging neighboring data points. If zero is provided, FORECAST.ETS will treat missing data points as zero. The aggregation argument is optional, and controls what function is used to aggregate data points when the timeline contains duplicate values. The default is 1, which specifies AVERAGE. Other options are given in the table below.
Errors
The FORECAST.ETS.STAT function will return errors as shown below.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
