Calculates a selected statistical value derived from historical time series data. A knowledge of the underlying statistics can increase understanding of the characteristics of a forecast made using the FORECAST.ETS.ADD function.
FORECAST.ETS.STAT.ADD (Values, Timeline, Statistic type, [Period length], [Data completion], [Aggregation])
Returns a number giving the value for the specified statistic. The exact form of this number depends on the selected statistic and the supplied historical time series data.
Values is an array of real numbers, representing the historic data to be used for forecasting. This argument can take the form of an explicit cell range, the name of a named range, the name of a database range, or an inline constant array. Values must comprise at least three values and all data in the array should be numeric.
Timeline is an array of dates, times, or numeric values, each giving the date / time associated with its corresponding entry in the Values array. This argument can take the form of an explicit cell range, the name of a named range, the name of a database range, or an inline constant array. Note the following:
- Timeline must have the same dimensions as Values.
- All entries in Timeline should be dates, times, or numbers.
- Timeline does not need to be sorted in any specific order on entry to the FORECAST.ETS.STAT.ADD function.
- The Timeline entries must be separated by an identifiable and consistent time step.
- It is possible for expected entries to be missing from Timeline, in which case FORECAST.ETS.STAT.ADD will generate data values for the missing time slots, controlled by the setting of the Data completion argument. Note however that only a proportion of the overall timeline can be missing; FORECAST.ETS.STAT.ADD reports an error if too much data is missing from the Timeline array.
- It is possible for the Timeline array to contain duplicate entries, in which case FORECAST.ETS.STAT.ADD will aggregate the duplicate entries as determined by the setting of the Aggregation argument.
Statistic type is an integer in the range [1, 9], which specifies the statistic to be returned. The available options are shown in the following table.
|1||Alpha - overall level smoothing parameter|
|2||Gamma - trend smoothing parameter|
|3||Beta - seasonal smoothing parameter|
|4||Mean absolute scaled error (MASE)|
|5||Symmetric mean absolute percentage error (SMAPE)|
|6||Mean absolute error (MAE)|
|7||Root-mean-square error (RMSE)|
|8||Step size detected in timeline, in units applicable to the timeline|
|9||Number of samples in period – either the value specified in the Period length argument, or the calculated number when Period length is set to 1|
Period length is a non-negative integer, or a reference to a cell containing that number, which is the number of samples in a period.
- A value of 0 indicates that your data does not contain periodic effects and that FORECAST.ETS.STAT.ADD should forecast using double exponential smoothing rather than triple exponential smoothing algorithms.
- A value of 1 indicates that FORECAST.ETS.STAT.ADD should determine the number of samples in a period automatically. This is the default applied if the argument is omitted.
- For other positive values, the specified period length is used.
Data completion is a logical value, or a reference to a cell containing that logical value, that determines how missing data points should be handled.
- A value of 0 or FALSE indicates that FORECAST.ETS.STAT.ADD should add missing data points with zero as the historical value.
- A value of 1 or TRUE indicates that FORECAST.ETS.STAT.ADD should add missing data points by interpolating between neighboring data points. This is the default applied if the argument is omitted.
Aggregation is an integer in the range [1, 7], or a reference to a cell containing such a value, indicating which method should be used to aggregate identical time values. The meaning of these seven values is given by the following table. 1 is the default applied if the argument is omitted.
The following errors and conditions may arise:
- If Values comprises less than three entries, then FORECAST.ETS.STAT.ADD reports a #VALUE! error.
- If any cell in Values is empty, or if Values contains any nun-numeric data, then FORECAST.ETS.STAT.ADD reports an invalid argument error (Err:502).
- If the Values and Timeline arrays do not have the same dimensions, then FORECAST.ETS.STAT.ADD reports an invalid argument error (Err:502).
- If any cell in Timeline is empty, or if Timeline contains any nun-numeric data, then FORECAST.ETS.STAT.ADD reports an invalid argument error (Err:502).
- If a constant step cannot be identified in the Timeline array, then FORECAST.ETS.STAT.ADD reports a #VALUE! error.
- If there are too many missing steps within the Timeline array, then FORECAST.ETS.STAT.ADD reports a #VALUE! error.
- If the Statistic type argument is a real number, it is converted to an integer. If, after any conversion, the value is less than 1 or greater than 9, then FORECAST.ETS.STAT.ADD reports an invalid argument error (Err:502).
- If Period length is not a non-negative integer value, then FORECAST.ETS.STAT.ADD reports a #NUM! error.
- If the Data completion argument is a real number, it is converted to an integer. If, after any conversion, the value is not equal to either 0 or 1, then FORECAST.ETS.STAT.ADD reports an invalid argument error (Err:502).
- If the Aggregation argument is a real number, it is converted to an integer. If, after any conversion, the value is less than 1 or greater than 7, then FORECAST.ETS.STAT.ADD reports an invalid argument error (Err:502).
- Exponential Smoothing is a method to smooth real values in time series to forecast probable future values. Exponential Triple Smoothing (ETS) is a set of algorithms in which both trend and periodical (seasonal) influences are processed. Exponential Double Smoothing (EDS) is an algorithm like ETS, but without the periodical influences. EDS produces linear forecasts.
- Note that FORECAST.ETS.STAT.ADD is not influenced by the value of the data point for which a forecast is calculated using FORECAST.ETS.ADD.
- The name space for FORECAST.ETS.STAT.ADD is
- For more information on exponential smoothing, visit Wikipedia's Exponential smoothing page.
The table below contains a timeline and its associated values:
|=FORECAST.ETS.STAT.ADD(Values; Timeline; $D$1; $E$1) where Values is a named range covering cells B2:B13 and Timeline is a named range covering cells A2:A13, with all cells containing the data shown in the table above.||The results for this example are presented in tabular form below. Cell $D$1 contains an integer specifying the required Statistic type for each case; cell $E$1 contains an integer specifying the required Period length for each case.||See table below.|
|Period Length (cell $E$1)|
|1 - Alpha||0.8369140625||0||0.7041015625|
|2 - Gamma||0||0.0615234375||0|
|3 - Beta||0||0.9990234375||1|
|4 - MASE||0.953343954834149||0.527312599681021||0.821621407496929|
|5 - SMAPE||0.118467932384609||0.063455073669477||0.102319506446129|
|6 - MAE||14.4908281134791||8.01515151515152||12.4886453939533|
|7 - RMSE||16.1405463229669||13.2349590635612||16.1623380622589|
|8 - Step size||1||1||1|
|9 - Samples in period||0||6||2|
Related LibreOffice functions:
Related (or similar) Excel functions: