FORECAST.ETS.CONFINT is an Excel function that is used to calculate the confidence interval for a future predicted value based on a series of historical data. This function is part of the Forecasting functions in Excel and is used to estimate future values based on historical data.
FORECAST.ETS.CONFINT Syntax
The syntax for the FORECAST.ETS.CONFINT function is as follows:
FORECAST.ETS.CONFINT(values, timeline, x, confidence, seasonality)
- values: This is the range of historical data that you want to use to predict future values.
- timeline: This is the range of dates or times that correspond to the historical data in the values range.
- x: This is the value for which you want to predict a future value.
- confidence: This is the level of confidence that you want to use for the prediction. The default value is 95%.
- seasonality: This is an optional argument that specifies the number of data points in each season. If your data has a seasonal pattern, you can use this argument to improve the accuracy of the prediction.
FORECAST.ETS.CONFINT Examples
Let’s look at some examples to see how the FORECAST.ETS.CONFINT function works:
Example 1:
Suppose you have the following data:
Date Sales 1/1/2019 100 2/1/2019 150 3/1/2019 200 4/1/2019 250 5/1/2019 300
You want to predict the sales for June 1, 2019 with 95% confidence. You can use the following formula:
=FORECAST.ETS.CONFINT(B2:B6,A2:A6,”6/1/2019″,0.95)
This will return a confidence interval for the predicted sales value for June 1, 2019.
Example 2:
Suppose you have the following data:
Date Sales 1/1/2019 100 2/1/2019 150 3/1/2019 200 4/1/2019 250 5/1/2019 300 6/1/2019 350 7/1/2019 400 8/1/2019 450 9/1/2019 500 10/1/2019 550 11/1/2019 600 12/1/2019 650
You want to predict the sales for January 1, 2020 with 99% confidence, taking into account the seasonal pattern of the data. You can use the following formula:
=FORECAST.ETS.CONFINT(B2:B13,A2:A13,”1/1/2020″,0.99,12)
This will return a confidence interval for the predicted sales value for January 1, 2020, taking into account the seasonal pattern of the data.
FORECAST.ETS.CONFINT Tips & Tricks
- The values and timeline ranges must be the same size.
- The x value must be a date or time value that is later than the last date or time value in the timeline range.
- The confidence value must be between 0 and 1.
- If your data has a seasonal pattern, you can use the seasonality argument to improve the accuracy of the prediction.
Common Mistakes When Using FORECAST.ETS.CONFINT
- Forgetting to include the timeline range in the function.
- Using a date or time value for x that is earlier than the last date or time value in the timeline range.
- Using a confidence value that is not between 0 and 1.
- Not taking into account the seasonal pattern of the data when using the seasonality argument.
Why Isn’t My FORECAST.ETS.CONFINT Working?
If your FORECAST.ETS.CONFINT function is not working, it could be due to one of the following reasons:
- You have not included the timeline range in the function.
- You have used a date or time value for x that is earlier than the last date or time value in the timeline range.
- You have used a confidence value that is not between 0 and 1.
- You have not taken into account the seasonal pattern of the data when using the seasonality argument.
FORECAST.ETS.CONFINT: Related Formulae
Here are some related Excel functions:
- FORECAST.ETS: This function is used to predict future values based on historical data.
- FORECAST.ETS.SEASONALITY: This function is used to calculate the seasonality of a data set.
- FORECAST.LINEAR: This function is used to predict future values based on linear regression.
- TREND: This function is used to calculate the linear trend of a data set.
- GROWTH: This function is used to calculate the exponential growth of a data set.