Excel FORECAST.ETS.CONFINT function

In statistics, a confidence interval is defined a range of estimates for an undefined parameter. The advantage of Excel is it helps its user to predict the future for unknow parameters as well. Sound too cool! Isn’t it? Microsoft Excel has introduced an inbuilt function FORECAST.ETS.CONFINT to help the users calculate the confidence interval for a forecasted value

In this tutorial, you will discover the definition of the FORECAST.ETS.CONFINT function, its syntax, parameter, error type and its reasons, and several real-time examples to understand how this formula works to calculate the confidence interval for a forecasted value.

What is FORECAST.ETS.CONFINT?

“The FORECAST.ETS.CONFINT is an inbuilt Excel function used to calculate the confidence interval for forecasted data. The confidence interval is defined as the measure of prediction accuracy-the lower the interval rate, the more confidence in foreknowing a specific data point.“

A confidence interval of 85% indicates that 85% of future points are expected to fall within this radius from the output delivered by the forecast function (with normal distribution). The confidence interval can help the user to catch the accuracy of the forecasted model. A smaller interval value would indicate more confidence in the forecast for the given data point.

The FORECAST.ETS.CONFINT was introduced with Excel 2016 and is only available with the latest versions of Excel 2016 such as in Excel for Office 365, Excel 2019.

NOTE: The Excel FORECAST.ETS.CONFINT function was introduced to be used along with the FORECAST.ETS function as a function to display the forecast accuracy.

Syntax

Parameters

Target_date (required) – This argument represents the data point for which the user wants to forecast a value. It accepts date, time or number values.

Values (required) – This argument signifies a range or array of historical data for which you want to predict future values.

Timeline (required) – This parameter represents an array of dates/times or independent numeric values with a constant step between them.

Confidence_level (optional) – This parameter represents a numeric data value between 0 and 1 that involves a level of confidence for the calculated interval. Mainly, it is supplied as a decimal number, although you can also supply percentage values. For example, if you want to provide an 80% confidence level, you can enter either 0.8 or 80%.

• If this argument is omitted, by default a confidence value of 95% is used, indicating 95% of future points are expected to fall within this radius from the output delivered by the forecast function.
• If this argument falls outside the range (0 – 1), the FORECAST.ETS.CONFINT function returns the #NUM! error.

Seasonality (optional) – This parameter represents a number indicating the length of the seasonal pattern:

• If this argument is omitted, by default Excel detects seasonality automatically by using positive, whole numbers.
• If 0 is supplied, no seasonality is taken and this function is represented by a linear forecast.

The maximum allowed seasonality is 8,760 (the number represent the total hours in a year). If you provide a value higher seasonality number, it will result in the #NUM! error.

Data completion (optional) – This parameter accounts for missing points.

• If the user supplies 1 or this parameter is omitted, the function fills in the missing points as the average of the neighbouring points (liner inrerpolation).
• If user supplies 0, this function considers the missing points as zeros.

Aggregation (optional) – This parameter specifies how to aggregate multiple data values with the same time stamp.

• If the user supplies 1 or this parameter is omitted, by default the AVERAGE function is used for aggregation.
• Your other options are: 2 – COUNT, 3 – COUNTA, 4 – MAX, 5 – MEDIAN, 6 – MIN and 7 – SUM.

Return

The FORECAST.ETS.CONFINT function returns a confidence interval for a forecasted value at a given point on a timeline.

Points to Remember

Below given are some useful points that will help you to predict the confidence of a forecasted value in a more specific way:

1. The FORECAST.ETS.CONFINT Function was introduced in Microsoft in Excel 2016.
2. This function works on advanced machine learning algorithms, for example, Exponential Triple Smoothing (ETS), to return a confidence interval for a forecasted value.
3. This function returns the #NUM! error if the provided target date is chronologically before the end of the historical timeline.
4. This function returns the #NUM! error if a constant step cannot be recognized in the specified timeline.
5. The FORECAST.ETS.CONFINT function returns the #VALUE! Error if the timeline holds duplicate data.
6. This function returns the #N/A error if the specified ranges of the timeline and values are not of the same size.
7. This function returns the #NUM! error if the confidence level falls outside the range (0,1).
8. This function returns the #NUM! error if the provided argument Seasonality is <0, or >8760, or the user has specified a non-numeric value.

Examples

Example 1: Calculate the confidence interval for the specified sample data set:

Refer to the below Excel data, where we are given the data (date representing timelines and Visitors representing values) of the website traffic for 21 days. let’s calculate the confidence interval for next 7 days of sample data:

Follow the below-given steps to compute the confidence interval for next 7 days using the Excel FORECAST.ETS.CONFINT() function:

Step 1: Calculate the website traffic using FORECAST.ETS

Since FORECAST.ETS.CONFINT function was introduced to be used along with the FORECAST.ETS function as a function to display the forecast accuracy. Therefore, we will predict the traffic of our website for the next 7 days and later using FORECAST.ETS.CONFINT function will check its accuracy.

Use the below given formula to calculate the future values for the given data set.

Drag the formula down the cells, and you will have all the forecasted visitors values for the next 7 days.

Step 2: Add another column to compute confidence interval.

Add a helper column and name it ‘Confidence Interval’. Next, place your cursor in the helper column adjacent to the first forecasted value so we can check its accuracy. In our case, we have selected cell D23 of our Excel worksheet.

Refer to the given below image:

STEP 3: Enter the FORECAST.ETS.CONFINT function

To check the accuracy of the forecasted output of FORECAST.ETS, we will compute the confidence interval. Therefore, start typing the function with the equal to (=) sign followed by the built-in FORECAST.ETS.CONFINT function. Our formula will become: = FORECAST.ETS.CONFINT(

STEP 4: Insert all the parameters

• At first, this function will ask you to specify the target_date parameter. Here, we will specify a numerical value for data point for which you want to forecast a value. The formula will be = FORECAST.ETS.CONFINT (A23,
• The next argument is value. This parameter should contain an array of historical data for which you want to predict future values. The formula will be = FORECAST.ETS.CONFINT (A23,$B$2:$B$22,
• The next argument we will specify an array of known dates or independent numeric values with a constant step between them. The formula will be = FORECAST.ETS.CONFINT (A23,$B$2:$B$22, $A$2:$A$22
• The last 4 arguments of these functions are optional. Therefore, we have omitted them. Excel will use the following default value in its place:
  • The argument confidence_level will be set to 95%.
  • The parameter Seasonality will be detected automatically.
  • For the parameter Data completion, 1 will be supplied as default option so as to complete missing points as the average of the neighboring points.
  • In the Aggregation parameter, value 1 will be passed as a default option so it can aggregate multiple data values with the identical timestamp by using the AVERAGE function.

Therefore, the overall formula becomes:

=FORECAST.ETS.CONFINT(A23, $B$2:$B$22, $A$2:$A$22)

NOTE: Make sure to lock the ranges with absolute cell references (like $A$2:$A$22) to prevent them from changing the cell references when replicating the FORECAST.ETS.CONFINT formula to other cells.

Step 5: The FORECAST.ETS.CONFINT function will compute the accuracy

As a result, the formula in D23 returns the output 128875.4. But now, what does this output means? It means that 95% of the time, the prediction for 18-Mar is expected to fall within 128875.4 of the forecasted value 108326.0577 (C23).

Step 6: Drag and Repeat the function to other cells

Select the C23 formula cell and take the mouse pointer towards the right corner of the selected box. You will notice that towards the right side, the cursor will change into a plus (+) icon.

Drag the icon to the following cells, and your surprise formula will be replicated down the cells where the absolute cell reference will be locked. It will successfully predict the confidence interval for the accuracy of predicted website traffic for the next 7 days.

Refer to the below given output:

Example 2: Calculate the confidence interval if the seasonality number is greater than 8,760.

The FORECAST.ETS.CONFINT function returns the #NUM! Error if the seasonality parameter holds a value greater than 8760.

Refer to the below Excel data, where we are given the data (date representing timelines and Visitors representing values.) Let’s calculate the confidence interval for next 7 days of sample data:

Follow the below-given steps to compute the confidence interval using the Excel FORECAST.ETS.CONFINT() function if the seasonality number is greater than 8,760:

Step 1: Calculate the website traffic using FORECAST.ETS

Since FORECAST.ETS.CONFINT function was introduced to be used along with the FORECAST.ETS function as a function to display the forecast accuracy. Therefore, we will predict the traffic of our website for the next 7 days and later using FORECAST.ETS.CONFINT function will check its accuracy.

Use the below given formula to calculate the future values for the given data set.

Drag the formula down the cells, and you will have all the forecasted visitors values for the next 7 days.

Step 2: Add another column to compute confidence interval.

Add a helper column and name it ‘Confidence Interval’. Next, place your cursor in the helper column adjacent to the first forecasted value so we can check its accuracy. In our case, we have selected cell D23 of our Excel worksheet.

Refer to the given below image:

STEP 3: Enter the FORECAST.ETS.CONFINT function

To check the accuracy of the forecasted output of FORECAST.ETS, we will compute the confidence interval. Therefore, start typing the function with the equal to (=) sign followed by the built-in FORECAST.ETS.CONFINT function. Our formula will become: = FORECAST.ETS.CONFINT(

STEP 4: Insert all the parameters

• At first, this function will ask you to specify the target_date parameter. Here, we will specify a numerical value for data point for which you want to forecast a value. The formula will be = FORECAST.ETS.CONFINT (A23,
• The next argument is value. This parameter should contain an array of historical data for which you want to predict future values. The formula will be = FORECAST.ETS.CONFINT (A23,$B$2:$B$22,
• The next argument we will specify an array of known dates or independent numeric values with a constant step between them. The formula will be = FORECAST.ETS.CONFINT (A23,$B$2:$B$22, $A$2:$A$22
• The argument confidence_level will be set to 95%. The formula will be = FORECAST.ETS.CONFINT (A23,$B$2:$B$22, $A$2:$A$22, 95%,
• In the parameter Seasonality, we will put a value of 90000. The formula will be = FORECAST.ETS.CONFINT (A23,$B$2:$B$22, $A$2:$A$22, 95%, 9000
• The last 2 parameters are omitted. Therefore, Excel will use the default value in its place:
  • For the parameter Data completion, 1 will be supplied as default option so as to complete missing points as the average of the neighboring points.
  • In the Aggregation parameter, value 1 will be passed as a default option so it can aggregate multiple data values with the identical timestamp by using the AVERAGE function.

Therefore, the overall formula becomes:

=FORECAST.ETS.CONFINT(A23, $B$2:$B$22, $A$2:$A$22, 95%, 90000)

NOTE: Make sure to lock the ranges with absolute cell references (for example $B$2:$B$22) to prevent the cells from changing the cell references when copying the FORECAST.ETS.CONFINT function to other cells.

Step 5: The FORECAST.ETS.CONFINT function will return #NUM! error

As a result, the formula in D23 returns the #NUM! error as an output 128875.4. Make sure, in the seasonality parameter the number value should be less than 8,760 (total number of hours in a year).