ExcelFunctions.net

Search Site:

+

Home » Excel-Built-In-Functions » Excel-Statistical-Functions » Excel-Forecast-Ets-Function

The Excel Forecast.Ets Function

Function Description

The Excel Forecast.Ets function uses an exponential smoothing algorithm to predict a future value on a timeline, based on a series of existing values.

The syntax of the function is:

Where the function arguments are:

target_date	-	A date/time for which you want to predict a value. (Must be after the last date/time in the historical timeline).
values	-	The array of historical known values for which you want to forecast the next point.
timeline	-	The independent array of dates/times, corresponding to each of the values. This must satisfy the following: The timeline array must have the same length as the values array; The dates/times in the timeline must have a consistent step length between them, although: Up to 30% of points may be missing and dealt with, according to the value of the [data completion] argument. There may be duplicates in the timeline, whose corresponding values will be aggregated, as defined by the [aggregation] argument. The dates/times in the timeline can be in any order.
[seasonality]	-	An optional argument indicating the algorithm that should be used to detect seasonality in the data. If supplied, this must be a positive integer between 0 and 8784, meaning: [seasonality] Algorithm 0 No seasonality (i.e. use the linear algorithm for the forecast). 1 (or omitted) Automatically calculate the seasonality and use positive, whole numbers for the length of the seasonal pattern. integer ≥ 2 & ≤ 8784 Use patterns of this length as the seasonality.
[data completion]	-	An optional argument specifying how the algorithm should handle missing points in the timeline. If supplied, the [data completion] argument can have the value 0 or 1 meaning: [data completion] Algorithm 0 Treat missing points as having the value zero. 1 (or omitted) Calculate the value for missing points to be the average of the neighbouring values.
[aggregation]	-	An optional argument specifying how the algorithm should aggregate values that have the same timestamp. If supplied, this can be any integer between 1 and 7 meaning: [aggregation] Aggregation Method 1 (or omitted) Average 2 Count 3 Counta 4 Max 5 Median 6 Min 7 Sum

Note: The Forecast.Ets function was first introduced in Excel 2016, so is not available in earlier versions of Excel. It is also not available in Excel 2016 for Mac.

Excel Forecast.Ets Function Example

The above spreadsheet on the right shows a set of monthly earnings between Jan-2015 and Apr-2017. These values are plotted in the chart below:

The Excel Forecast.Ets function can be used to forecast the value of the earnings for May 2017, as follows:

This gives the result 1461.632054.

Note that, in the above example:

• The target_date, May-2017, is stored in cell A30 of the example spreadsheet.
• Although the timeline array (stored in cells A2:A29 of the example spreadsheet) is ordered chronologically, this is not essential for the Forecast.Ets function.
• The [seasonality], [data completion], and [aggregation] arguments have been omitted from the function and so use the default values.
  (I.e. [seasonality] = 1; [data completion] = 1; [aggregation] = 0).

For further details of the Excel Forecast.Ets function, see the Microsoft Office website

Forecast.Ets Function Errors

If you get an error from the Forecast.Ets function, this is likely to be one of the following:

Disclaimer   Privacy Policy   Cookies Policy
Copyright © 2008-2023 ExcelFunctions.net