# The Excel Forecast.Ets.Confint Function

Related Function:
Forecast.Ets

## Function Description

The Excel Forecast.Ets.Confint function calculates the confidence interval for a forecast value on a timeline.

The syntax of the function is:

FORECAST.ETS.CONFINT( target_date, values, timeline, [confidence_level], [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.
[confidence_level] -

An optional numeric value, indicating the confidence level for the calculated confidence interval.

If supplied, this must be an integer between 0 and 1 (exclusive).

If omitted, the [confidence_level] is set to the default value 0.95 (i.e. a 95% confidence level).
[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.Confint 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.Confint Function Example

A B
1 Month Earnings
2 Jan-2015 879
3 Feb-2015 1259
4 Mar-2015 1230
5 Apr-2015 1471
6 May-2015 1638
7 Jun-2015 1371
8 Jul-2015 1562
9 Aug-2015 1526
10 Sep-2015 1125
11 Oct-2015 1340
12 Nov-2015 830
13 Dec-2015 726
14 Jan-2016 917
15 Feb-2016 1028
16 Mar-2016 1449
17 Apr-2016 1225
18 May-2016 1437
19 Jun-2016 1637
20 Jul-2016 1345
21 Aug-2016 1148
22 Sep-2016 1075
23 Oct-2016 928
24 Nov-2016 852
25 Dec-2016 752
26 Jan-2017 989
27 Feb-2017 1304
28 Mar-2017 1192
29 Apr-2017 1265
30 May-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 Forecast.Ets function has previously calculated the forecast earnings for May 2017 to be 1461.632054.

The Excel Forecast.Ets.Confint function can be used to calculate a confidence interval (with a confidence level of 95%) for this forecast earnings value, as follows:

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

This gives the result 202.1409609.

Therefore 95% of the time, the earnings value for May 2017 is expected to fall within 202.1409609 of the calculated value 1461.632054.

I.e. 95% of the time, the earnings value for May 2017 is expected to fall in the range:

1461.632054 ± 202.1409609.

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

## Forecast.Ets.Confint Function Errors

If you get an error from the Forecast.Ets.Confint 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 [confindence_level] value is not within the valid range 0 - 1 (exclusive); 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 [confindence_level], [seasonality], [data completion] or [aggregation] arguments is non-numeric.