The Excel Forecast.Ets.Confint Function
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:
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.|
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.
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).
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:
|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.|
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:
|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.|
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:
|1 (or omitted)||Average|
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
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:
|#N/A||-||Occurs if the supplied values and timeline arrays have different lengths.|
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.|