Documentation/Calc Functions/FORECAST.ETS.PI.MULT

    From The Document Foundation Wiki

    Function name:

    FORECAST.ETS.PI.MULT

    Category:

    Statistical Analysis

    Summary:

    Calculates the prediction interval for a future value estimated using multiplicative exponential smoothing algorithms, as described for the FORECAST.ETS.MULT function. The prediction interval gives an indication of the likely accuracy of the estimated future value, for a specified confidence level.

    FORECAST.ETS.PI.MULT can be used in conjunction with the following related functions:

    Syntax:

    FORECAST.ETS.PI.MULT(Target; Values; Timeline; [Confidence level]; [Period length]; [Data completion]; [Aggregation])

    Returns:

    Returns a real number, which is the prediction interval associated with the value that would be forecast by FORECAST.ETS.MULT for the same time series data and settings.

    Arguments:

    Target is a single date, time, or numeric value, or a reference to a cell containing such a value, that is the data point for which a prediction interval is to be calculated. Target may also refer to a range of cells, with each cell containing a different value specifying a data point of interest - this allows FORECAST.ETS.PI.MULT to be used in an array formula to simplify the process of calculating multiple prediction intervals. FORECAST.ETS.PI.MULT generates an error if the value of Target is not after the latest value specified in the Timeline argument.

    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.PI.MULT 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.PI.MULT 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.PI.MULT 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.PI.MULT will aggregate the duplicate entries as determined by the setting of the Aggregation argument.

    Confidence level is a percentage value used in calculating the prediction interval. Confidence level is a real number expressed as a percentage or a decimal fraction, or a reference to a cell containing that number. It lies in the range [0, 1] and, if omitted, the default value of 0.95 is used.

    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.PI.MULT should forecast using double exponential smoothing rather than triple exponential smoothing algorithms.
    • A value of 1 indicates that FORECAST.ETS.PI.MULT 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.PI.MULT should add missing data points with zero as the historical value.
    • A value of 1 or TRUE indicates that FORECAST.ETS.PI.MULT 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.

    ETS aggregation functions
    Value Function
    1 AVERAGE
    2 COUNT
    3 COUNTA
    4 MAX
    5 MEDIAN
    6 MIN
    7 SUM

    The following errors and conditions may arise:

    • If Target is non-numeric, then FORECAST.ETS.PI.MULT reports a #NUM! error.
    • If Target is not after the latest entry in the Timeline array, then FORECAST.ETS.PI.MULT reports either a #NUM! error (before the latest entry) or an invalid argument error (Err:502; equal to the latest entry).
    • If Values comprises less than three entries, then FORECAST.ETS.PI.MULT reports a #VALUE! error.
    • If any cell in Values is empty, or if Values contains any nun-numeric data, then FORECAST.ETS.PI.MULT reports an invalid argument error (Err:502).
    • If the Values and Timeline arrays do not have the same dimensions, then FORECAST.ETS.PI.MULT 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.PI.MULT reports an invalid argument error (Err:502).
    • If a constant step cannot be identified in the Timeline array, then FORECAST.ETS.PI.MULT reports a #VALUE! error.
    • If there are too many missing steps within the Timeline array, then FORECAST.ETS.PI.MULT reports a #VALUE! error.
    • If Confidence level is non-numeric, then FORECAST.ETS.PI.MULT reports a #NUM! error.
    • If Confidence level is less than 0 or greater than 1, then FORECAST.ETS.PI.MULT reports an invalid argument error (Err:502).
    • If Period length is not a non-negative integer value, then FORECAST.ETS.PI.MULT 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.PI.MULT 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.PI.MULT reports an invalid argument error (Err:502).

    Additional details:

    • 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.
    • FORECAST.ETS.PI.MULT calculates with the model:
    [math]\displaystyle{ forecast~=~(base\:value~+~(trend \times ∆x))~\times~periodical\:aberration }[/math]
    • There is no exact mathematical algorithm to forecast a prediction interval or accuracy indicator, although various approximations are available. FORECAST.ETS.PI.MULT uses an approximation based on multiple calculations with random variations in the standard deviation of the historical time series data set. A consequence of this approach is that repeated calls to the FORECAST.ETS.PI.MULT function for the same arguments can return different results – this behavior is intentional but can be misleading if not understood (see Bug 121396 for more background information).
    • The name space for FORECAST.ETS.PI.MULT is ORG.LIBREOFFICE.FORECAST.ETS.PI.MULT.
    • For more information on exponential smoothing, visit Wikipedia's Exponential smoothing page.
    • For more information on prediction intervals, visit Wikipedia's Prediction interval page.

    Examples:

    The table below contains a timeline and its associated values:

    ETS algorithm sample data
    A B
    1 Timeline Values
    2 2013-01-01 112
    3 2013-02-01 118
    4 2013-03-01 132
    5 2013-04-01 100
    6 2013-05-01 121
    7 2013-06-01 135
    8 2013-07-01 148
    9 2013-08-01 148
    10 2013-09-01 136
    11 2013-10-01 119
    12 2013-11-01 104
    13 2013-12-01 118
    Note pin.svg

    Note:
    Be aware that successive calls to FORECAST.ETS.PI.MULT with the same argument values may return different results. While this behaviour is intentional and reflects the approximate nature of this accuracy indicator, it means that the values shown in the Returns column below may be different from those obtained on your computer.

    Formula Description Returns
    =FORECAST.ETS.PI.MULT(DATE(2014;1;1); Values; Timeline) 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. Here the function returns a prediction interval associated with a forecast for 2014-01-01 based on the historic data for 2013. The Confidence level argument is omitted and so FORECAST.ETS.PI.MULT applies the default value of 95%. The Period length argument is omitted and so FORECAST.ETS.PI.MULT determines the number of samples in a period automatically (this will be 6 and this could be checked by charting the data or by using the formula =FORECAST.ETS.SEASONALITY(Values; Timeline)). The Data completion argument is omitted but this argument is redundant when there are no missing data points within the timeline. The Aggregation argument is omitted but this argument is redundant when there are no duplicated time values within the timeline. 23.6933016810569
    =FORECAST.ETS.PI.MULT(DATE(2014;1;1); Values; Timeline; 0.95 ; 4) with Values and Timeline as in the previous example. Here the function again returns a prediction interval associated with a forecast for 2014-01-01 based on the historic data for 2013. However, in this case, the Period length argument is set to 4. While this value does not accurately reflect the historic data and timeline, FORECAST.ETS.PI.MULT does not report an error but returns a significantly larger value than in the previous example. 42.8665013875922
    =FORECAST.ETS.PI.MULT(DATE(2014;1;1); Values; Timeline; 95%; 0) with Values and Timeline as in the previous example. Here the function again forecasts the value for 2014-01-01 based on the historic data for 2013. However, in this case, the Period length argument is set to 0. This choice forces FORECAST.ETS.PI.MULT to assume no seasonality and calculate a prediction interval for a forecast made with the double exponential smoothing algorithm. 31.6348894838155
    =FORECAST.ETS.PI.MULT(6; {3,1,9,2,7,6}; {1,2,2,3,4,5}; ; ; ; 4) Here the timeline contains duplicate entries for the time value 2 and the Aggregation argument is set to 4, indicating that the maximum of the two values for that time should be used. 3.20766779379733

    Related LibreOffice functions:

    FORECAST

    FORECAST.ETS.ADD

    FORECAST.ETS.MULT

    FORECAST.ETS.PI.ADD

    FORECAST.ETS.SEASONALITY

    FORECAST.ETS.STAT.ADD

    FORECAST.ETS.STAT.MULT

    FORECAST.LINEAR

    ODF standard:

    None

    Related (or similar) Excel functions:

    None