# Documentation/Calc Functions/FORECAST.ETS.ADD

TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate

## Function name:

FORECAST.ETS.ADD

## Category:

Statistical Analysis

## Summary:

Calculates a future value based on historical time series data. FORECAST.ETS.ADD uses additive triple exponential smoothing algorithms to process time series data that includes both trend and absolute seasonal effects. FORECAST.ETS.ADD uses double exponential smoothing algorithms when there are no seasonal effects in the historical data. FORECAST.ETS.ADD can be used in conjunction with the following related functions:

- FORECAST.ETS.PI.ADD - calculates the prediction interval for a forecast made with FORECAST.ETS.ADD.
- FORECAST.ETS.SEASONALITY - calculates the number of samples in the seasonal period.
- FORECAST.ETS.STAT.ADD - returns statistical values relating to a forecast made with FORECAST.ETS.ADD.

## Syntax:

FORECAST.ETS.ADD(**Target**; **Values**; **Timeline**; [**Period length**]; [**Data completion**]; [**Aggregation**])

## Returns:

Returns a real number, which is the forecast value at the target time.

## 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 forecast 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.ADD to be used in an array formula to simplify the process of making multiple forecasts. It is expected that in many cases, forecasts will be made for values of **Target** that are after the latest value specified in the **Timeline** argument. However, FORECAST.ETS.ADD will only automatically generate an error if the value of **Target** is before the earliest 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.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.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.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.ADD will aggregate the duplicate entries as determined by the setting of the**Aggregation**argument.

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

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.ADD reports a #NUM! error. - If
**Target**is before the earliest entry in the**Timeline**array, then FORECAST.ETS.ADD reports a #NUM! error. - If
**Values**comprises less than three entries, then FORECAST.ETS.ADD reports a #VALUE! error. - If any cell in
**Values**is empty, or if**Values**contains any nun-numeric data, then FORECAST.ETS.ADD reports an invalid argument error (Err:502). - If the
**Values**and**Timeline**arrays do not have the same dimensions, then FORECAST.ETS.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.ADD reports an invalid argument error (Err:502). - If a constant step cannot be identified in the
**Timeline**array, then FORECAST.ETS.ADD reports a #VALUE! error. - If there are too many missing steps within the
**Timeline**array, then FORECAST.ETS.ADD reports a #VALUE! error. - If
**Period length**is not a non-negative integer value, then FORECAST.ETS.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.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.ADD 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.ADD calculates with the model:

- [math]\displaystyle{ forecast~=~base\:value~+~(trend \times ∆x)~+~periodical\:aberration }[/math]

- The name space for FORECAST.ETS.ADD is
`COM.MICROSOFT.FORECAST.ETS`

. - For more information on exponential smoothing, visit Wikipedia's Exponential smoothing page.

## Examples:

The table below contains a timeline and its associated values:

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 |

Formula | Description | Returns |
---|---|---|

=FORECAST.ETS.ADD(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 forecasts the value for 2014-01-01 based on the historic data for 2013. The Period length argument is omitted and so FORECAST.ETS.ADD 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. Note that the formula `=FORECAST.ETS.ADD(DATE(2014;1;1); Values; Timeline; 1; 1; 1)` which explicitly sets the final three arguments to their defaults, returns the same value. |
157.166666666667 |

=FORECAST.ETS.ADD(DATE(2014;1;1); Values; Timeline; 4) 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 4. While this value does not accurately reflect the historic data and timeline, FORECAST.ETS.ADD does not report an error but returns a different value than in the previous example. |
113.251442038722 |

=FORECAST.ETS.ADD(DATE(2014;1;1); Values; Timeline; 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.ADD to assume no seasonality and so it applies the double exponential smoothing algorithm. |
116.849429588038 |

=FORECAST.ETS.ADD(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. Note that the formula `=FORECAST.ETS.ADD(6; {3,9,2,7,6}; {1,2,3,4,5})` (pre-processing the input data to remove the duplication manually) returns the same result. |
6 |

## Related LibreOffice functions:

## ODF standard:

None

## Related (or similar) Excel functions:

FORECAST.ETS