The following time series forecasting functions can be used to predict future values based on historical data. These functions use advanced machine learning algorithms, such as Exponential Triple Smoothing (ETS).

  • These functions were introduced in Excel 2016 and so are not available in earlier versions.

The FORECAST.ETS function calculates or predicts a future value based on historical values by using the AAA version of the Exponential Smoothing (ETS) algorithm. The predicted value is a continuation of the historical values in the specified target date, which should be a continuation of the timeline. You can use this function to predict future sales, inventory requirements, or consumer trends.

Syntax

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

Arguments

Argument Description
target_date The data point for which you want to predict a value
values The historical values for which you want to forecast the next points
timeline

The independent array or range of numeric data

  The timeline dates must be organized with a constant step between the different points, e.g. a monthly timeline with values on the 1st of every month, a yearly timeline, or a timeline of numerical indices
  The timeline is not required to be sorted – FORECAST.ETS will sort it implicitly for calculations
[seasonality]

Optional. A numeric value specifying the type of seasonality

  0 No seasonality – the prediction will be linear
  1 (default) Automatic seasonality – positive, whole numbers are used for the length of the seasonal pattern
[data_completion]

Optional. The timeline requires a constant step between data points, however, FORECAST.ETS will automatically adjust for up to 30% missing data

  0 indicates the algorithm is to account for missing points as zeros
  1 (default) accounts for missing points by assuming them to be the average of the neighboring points
[aggregation]

Optional. The timeline requires a constant step between data points, however, FORECAST.ETS will aggregate multiple points which have the same time stamp. The aggregation parameter is a numeric value indicating which method will be used to aggregate several values with the same time stamp

  0 uses AVERAGE
    other options are SUM, COUNT, COUNTA, MIN, MAX, MEDIAN

The FORECAST.ETS.SEASONALITY function returns the length of the repetitive pattern Excel detects for the specified time series. FORECAST.ETS.Seasonality can be used following FORECAST.ETS to identify which automatic seasonality was detected and used in FORECAST.ETS. While it can also be used independently of FORECAST.ETS, the functions are tied since the seasonality detected in this function is identical to the one used by FORECAST.ETS, considering the same input parameters that affect data completion.

Syntax

=FORECAST.ETS.SEASONALITY(target_date,values,timeline,[seasonality],[data_completion])

Note: All arguments for the FORECAST.ETS.SEASONALITY follow the same semantics as FORECAST.ETS.

The FORECAST.LINEAR function calculates, or predicts, a future value by using existing values. The predicted value is a y-value for a given x-value. The known values are existing x-values and y-values, and the new value is predicted by using linear regression. You can use this function to predict future sales, inventory requirements, or consumer trends.

Syntax

=FORECAST.LINEAR(x,known_y’s,known_x’s)

Arguments

Argument Description
x The data point for which you want to predict a value
known_y’s The dependent array or range of data
known_x’s The independent array or range of data

The FORECAST.ETS.CONFINT returns a confidence interval for the forecast value at the specified target date. A confidence interval of 95% means that 95% of future points are expected to fall within this radius from the result FORECAST.ETS forecasted (with normal distribution). Using confidence interval can help grasp the accuracy of the predicted model. A smaller interval would imply more confidence in the prediction for this specific point.

Syntax

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

Arguments

Note: All arguments for the FORECAST.ETS.CONFINT follow the same semantics as FORECAST.ETS with one additional argument:

Argument Description
[confidence_level] Optional. A numerical value between 0 and 1 (exclusive), indicating a confidence level for the calculated confidence interval

The FORECAST.ETS.STAT returns a statistical value as a result of time series forecasting.

Syntax

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

All arguments for the FORECAST.ETS.CONFINT follow the same semantics as FORECAST.ETS with one additional argument:

Arguments

Argument Description
statistic_type A numeric value between 1 and 8, indicating which statistic will be returned for the calculated forecast. The following optional statistics can be returned:

  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 symmetric 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

Common Function Error(s)

Problem What went wrong
#VALUE! Occurs if the timeline contains duplicate values
#NUM! Occurs if either:

  the target_date is chronologically before the end of the historical timeline
  a constant step can’t be identified in the provided timeline
  the seasonality argument is not a positive, whole number
  the seasonality argument is > 8,760 (the number of hours in a year)
  the confidence_level argument is ≤ 0 or ≥ 1
#N/A Occurs if the ranges of the timeline and values aren’t of same size