# 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:

FORECAST.ETS( target_date, values, timeline, [seasonality], [data completion], [aggregation] )

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
0No 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
0Treat 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
2Count
3Counta
4Max
5Median
6Min
7Sum

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

AB
1MonthEarnings
2Jan-2015879
3Feb-20151259
4Mar-20151230
5Apr-20151471
6May-20151638
7Jun-20151371
8Jul-20151562
9Aug-20151526
10Sep-20151125
11Oct-20151340
12Nov-2015830
13Dec-2015726
14Jan-2016917
15Feb-20161028
16Mar-20161449
17Apr-20161225
18May-20161437
19Jun-20161637
20Jul-20161345
21Aug-20161148
22Sep-20161075
23Oct-2016928
24Nov-2016852
25Dec-2016752
26Jan-2017989
27Feb-20171304
28Mar-20171192
29Apr-20171265
30May-2017

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:

=FORECAST.ETS( A30, B2:B29, A2:A29 )

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:

Common Errors
 #N/A - Occurs if the supplied values and timeline arrays have different lengths. #NUM! - Occurs if either:A consistent step size cannot be identified in the dates/times of the supplied timeline;The supplied [seasonality] value is not within the valid range 0 - 8784;The supplied [data completion] is not equal to 0 or 1;The supplied [aggregation] value is not within the valid range 1 - 7. #VALUE! - Occurs if one or more of the supplied [seasonality], [data completion] or [aggregation] arguments is non-numeric.