Skip to content

Latest commit

 

History

History
60 lines (41 loc) · 4.52 KB

Excel.worksheetfunction.forecast_ets_stat.md

File metadata and controls

60 lines (41 loc) · 4.52 KB
title keywords f1_keywords ms.assetid ms.date ms.localizationpriority
WorksheetFunction.Forecast_ETS_STAT method (Excel)
vbaxl10.chm137472
vbaxl10.chm137472
6b1c0256-3146-4dc5-3f8a-27e61a982fee
05/22/2019
medium

WorksheetFunction.Forecast_ETS_STAT method (Excel)

Returns a statistical value as a result of time series forecasting.

Syntax

expression.Forecast_ETS_STAT (Arg1, Arg2, Arg3, Arg4, Arg5, Arg6)

expression A variable that represents a WorksheetFunction object.

Parameters

Name Required/Optional Data type Description
Arg1 Required Variant Values: the historical values, for which you want to forecast the next points.
Arg2 Required Variant Timeline: the independent array or range of dates or numeric data. The values in the timeline must have a consistent step between them and can't be zero. See Remarks.
Arg3 Required Double Statistic_type: A numeric value between 1 and 8, indicating which statistic will be returned for the calculated forecast.
Arg4 Optional Variant Confidence level: A numerical value between 0 and 1 (exclusive), indicating a confidence level for the calculated confidence interval. See Remarks.
Arg5 Optional Variant Data completions: Although the timeline requires a constant step between data points, Forecast_ETS_STAT supports up to 30% missing data, and automatically adjusts for it. See Remarks.
Arg6 Optional Variant Aggregation: Although the timeline requires a constant step between data points, Forecast_ETS_STAT aggregates multiple points that have the same time stamp. See Remarks.

Return value

Double

Remarks

It'sn't necessary to sort the timeline (Arg2), because Forecast_ETS_STAT sorts it implicitly for calculations. If Forecast_ETS_STAT can't identify a constant step in the timeline, it returns run-time error 1004. If the timeline contains duplicate values, Forecast_ETS_STAT also returns an error. If the ranges of the timeline and values aren't all of the same size, Forecast_ETS_STAT returns run-time error 1004.

The statistic_type parameter (Arg3) indicates which statistic is requested by this function. The following optional statistics can be returned:

  • Alpha parameter of ETS algorithm. Returns the base value parameter—a higher value gives more weight to recent data points.
  • Beta parameter of ETS algorithm. Returns the trend value parameter—a higher value gives more weight to the recent trend.
  • Gamma parameter of ETS algorithm. Returns the trend value parameter—a higher value gives more weight to the recent trend.
  • MASE metric. Returns the mean absolute scaled error metric, a measure of the accuracy of forecasts.
  • SMAPE metric. Returns the symmetric mean absolute percentage error metric, an accuracy measure based on percentage errors.
  • MAE metric. Returns the symmetric mean absolute percentage error metric, an accuracy measure based on percentage errors.
  • RMSE metric. Returns the root mean squared error metric, a measure of the differences between predicted and observed values.
  • Step size detected. Returns the step size detected in the historical timeline.

A confidence interval (Arg4) of 95% means that 95% of future points are expected to fall within this radius from the result that Forecast_ETS forecasted (with normal distribution). Using confidence intervals can help you grasp the accuracy of the predicted model. A smaller interval implies more confidence in the prediction for this specific point.

For example, for a 90% confidence interval, a 90% confidence level is computed (90% of future points are to fall within this radius from prediction). The default value is 95%. For numbers outside of the range (0,1), Forecast_ETS_STAT returns an error.

Passing 0 for the data completions parameter (Arg5) instructs the algorithm to account for missing points as zeros. The default value of 1 accounts for missing points by computing them to be the average of the neighboring points. If there is more than 30% missing data, Forecast_ETS_STAT returns run-time error 1004.

The aggregation parameter (Arg6) is a numeric value specifying the method to use to aggregate several values that have the same time stamp. The default value of 0 specifies AVERAGE, while other numbers between 1 and 6 specify SUM, COUNT, COUNTA, MIN, MAX, and MEDIAN.

[!includeSupport and feedback]