Excel FORECAST.ETS.CONFINT functionIn 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 realtime 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 accuracythe 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
FORECAST.ETS.CONFINT(target_date, values, timeline, [confidence_level], [seasonality], [data completion], [aggregation])
ParametersTarget_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%.
Seasonality (optional)  This parameter represents a number indicating the length of the seasonal pattern:
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.
Aggregation (optional)  This parameter specifies how to aggregate multiple data values with the same time stamp.
ReturnThe FORECAST.ETS.CONFINT function returns a confidence interval for a forecasted value at a given point on a timeline. Points to RememberBelow given are some useful points that will help you to predict the confidence of a forecasted value in a more specific way:
ExamplesExample 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 belowgiven 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.
=FORECAST.ETS(A23, $B$2:$B$22, $A$2:$A$22)
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 builtin FORECAST.ETS.CONFINT function. Our formula will become: = FORECAST.ETS.CONFINT( STEP 4: Insert all the parameters
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 18Mar 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 belowgiven 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.
=FORECAST.ETS(A23, $B$2:$B$22, $A$2:$A$22)
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 builtin FORECAST.ETS.CONFINT function. Our formula will become: = FORECAST.ETS.CONFINT( STEP 4: Insert all the parameters
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).
Next TopicExcel FORECAST.ETS Function
