Summary

The Excel FORECAST.ETS function predicts a value based on existing values that follow a seasonal trend. FORECAST.ETS can be used to predict numeric values like sales, inventory, expenses, etc. with a seasonal pattern.

Purpose 

Predict value with a seasonal trend

Return value 

Predicted value

Syntax

=FORECAST.ETS(target_date,values,timeline,[seasonality],[data_completion],[aggregation])
  • target_date - The time or period for the prediction (x value).
  • values - Existing or historical values (y values).
  • timeline - Numeric timeline values (x values).
  • seasonality - [optional] Seasonality calculation (0 = no seasonality, 1 = automatic, n = season length in timeline units).
  • data_completion - [optional] Missing data treatment (0 = treat as zero, 1 = average). Default is 1.
  • aggregation - [optional] Aggregation behavior. Default is 1 (AVERAGE). See other options below.

How to use 

The FORECAST.ETS function predicts a value based on existing values that follow a seasonal trend. FORECAST.ETS can be used to predict numeric values like sales, inventory, expenses, etc. with a seasonal pattern.

To calculate predicted values, FORECAST.ETS uses something called triple exponential smoothing. This is an algorithm that applies overall smoothing, trend smoothing, and seasonal smoothing.

Example

In the example shown above, the formula in cell D13 is:

=FORECAST.ETS(B13,sales,periods,4)

where sales (C5:C12) and periods (B5:B12) are named ranges. With these inputs, the FORECAST.ETS function returns 618.29 in cell D13. As the formula is copied down the table, FORECAST.ETS returns predicted values in D13:D16, using values in column B for target date.

The chart to the right shows this data plotted in a scatter plot.

Note: Cell D12 is set equal to C12 to connect the existing values to the predicted values in the chart.

Argument notes

The target_date argument represents the point on the timeline that a prediction should be calculated.

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, as in the example shown.

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 is 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.

Note: It is better to perform aggregation before using FORECAST.ETS to make forecasting as accurate as possible.

Value Behavior
1 (or omitted) AVERAGE
2 COUNT
3 COUNTA
4 MAX
5 MEDIAN
6 MIN
7 SUM

Errors

The FORECAST.ETS function will return errors as shown below.

Error Cause
#VALUE!
  • target_date is not numeric
  • seasonality is not numeric
  • data_completion is not numeric
  • aggregation  is not numeric
#N/A
  • values and timeline are not the same size
#NUM
  • Consistent step cannot be determined in timeline
  • All timeline values are the same
  • The value for seasonality is not within 0-8784
  • The value for data_completion is not 0 or 1
  • The value for aggregation is not within 1-7
Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

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.