Excel FORECAST.ETS.CONFINT Function

by | Mar 4, 2022 | Excel Formulas

Excel FORECAST ETS CONFINT Function

The FORECAST.ETS.CONFINT function is a Statistical function that calculates a confidence interval for a forecasted value by using existing values with a seasonal pattern. The function is using the AAA version (additive error, additive trend and additive seasonality) of the Exponential Smoothing (ETS) algorithm. Use this function with FORECAST.ETS function to see the accuracy of the forecasting. In this guide, we’re going to show you how to use the FORECAST ETS CONFINT function and go over some tips and error handling methods.

Supported versions

  • Excel 2016 or newer
  • Not supported Excel for the Web, iOS, or Android.

FORECAST ETS CONFINT Function Syntax

FORECAST.ETS.CONFINT(target_date, values, timeline, [confidence_level], [seasonality], [data_completion], [aggregation])

Arguments

target_date The data point to use to predict a value. Target date should be chronologically after than the end of the historical timeline.
values The historical values in data points. (y values)
timeline The historical data points (dates). (x values)
[confidence_level] Optional. A numerical value between 0 and 1 determining a confidence level for the calculated confidence interval. The default value is 95%.
[seasonality]

Optional. A positive numeric value represents the periods in a season.

  • 0: No seasonality, linear prediction.
  • 1: Automatic. Default value.
[data_completion]

Optional. This determines how Excel treats missing data.

  • 0: Treat as zero (0).
  • 1: The average of the neighboring points. Default values.
[aggregation]

Optional. The aggregation method for the values with the same time stamp.

  • 1: AVERAGE (Default)
  • 2: COUNT
  • 3: COUNTA
  • 4: MAX
  • 5: MEDIAN
  • 6: MIN
  • 7: SUM

FORECAST.ETS.CONFINT Function Examples

Default form

The FORECAST.ETS.CONFINT function needs the date for the data point you want to forecast (target_date) and the historical data (values, timeline) to work in its default state. The remaining three arguments are optional.

=FORECAST.ETS.CONFINT([@Date],Historical_Values,Historical_Dates)

  • target_date: B48 ([@Date])
  • values: C5:C47 (Historical_Values)
  • timeline: B5:B47 (Historical_Dates)

In this form, the confidence level is 95% which means that 95% of the time the predicted values are expected to fall within minus/plus (12,290,22 ~ 12,944.07) the result from what the FORECAST.ETS calculates (12,617.15). Excel calculates the seasonality automatically. If there are empty values, Excel fills them according to the average of the neighboring points. Finally, if there are multiple values on a same data point (date), Excel aggregates them by calculating the average of those. You can alter each of these assumptions by populating the optional arguments.

Charting

A single forecasted value will not tell you much. The best way to review and compare the forecasted data is to forecast for more points and use a line chart to see the trend. All you need to do is to generate more data points and copy the formula. Just pay attention to absolute and relative references, since you will want to keep ranges of the historical data.

In the following example you can see the chart which is generated from the formulas using optional arguments J5, J6, J7 and J8. The dotted lines represent the limits of confidence value. Smaller area, higher accuracy.

Excel FORECAST ETS CONFINT Function

Note that the cells D47, F47 and G47 have the same value as the last historical data just to start lines from the same point.

Optional arguments

Using optional arguments are related to the dataset and the data origin. If you are in doubt, do not hesitate to try a different combination to find proper options for your data. Here is an example of how different options can alter the predicted values.

Download Workbook

Remarks

  • The FORECAST ETS CONFINT will return the #NUM! error for numbers not between 0 and 1.
  • If the target_date is chronologically before the end of the historical timeline, FORECAST.ETS.CONFINT returns the #NUM! error.
  • The date values in the timeline range aren't required to be sorted. However, if a constant step can't be identified in the provided timeline, the function returns #NUM!
  • If the timeline range contains duplicate values, FORECAST.ETS.CONFINT will return the #VALUE! error.
  • If the ranges of the timeline and values aren't of same size, the function will return the #N/A
  • The maximum value for the seasonality is 8760 (number of hours in a year). Any number above will return #NUM!

Excel Forecasting Functions

Forecasting with linear regression FORECAST.LINEAR
Forecasting using Exponential Triple Smoothing FORECAST.ETS
Seasonality in a forecasting using Exponential Triple Smoothing FORECAST.ETS.SEASONALITY
Statistical values in a forecasting using Exponential Triple Smoothing FORECAST.ETS.STAT