What Is FORECAST.ETS.CONFINT Excel Function?
The FORECAST.ETS.CONFINT Excel function is categorized under financial functions used for analysis and decision-making. It allows users to estimate the confidence interval for future predictions based on historical data. This function employs the Exponential Smoothing (ETS) algorithm, which is widely recognized as one of the most accurate and reliable forecasting methods.
The FORECAST.ETS.CONFINT Excel function requires a time series dataset and additional input parameters such as alpha as the smoothing factor and beta as the trend-dampening factor.
This article demonstrates the application of the FORECAST.ETS.CONFINT Excel function to achieve a desired outcome.
To utilize this formula, simply input it in cell E2, as illustrated below:
=FORECAST.ETS.CONFINT(D2,B2:B4, A2:A4)
Upon execution, the resulting value will be promptly displayed.
Syntax
Target_date – This is the mandatory argument. This is the upcoming date and time for which you desire to make a forecast.
Value – This is the mandatory argument. The range of cells that encompass the historical values upon which your forecast will be founded.
Timeline – This is the mandatory argument. These are the dates and times to which your values are associated. They can either be in the form of a date or time, or represented as a numerical value.
Confidence_level – This is the optional argument. A numerical value greater than 0 and less than 1 is used to indicate a confidence level.
Seasonality – This is the optional argument. This numeric value represents the length of the seasonal pattern. By default, it is set to 1, indicating that Excel will automatically detect the seasonality. On the other hand, a value of 0 signifies no seasonality, resulting in a linear forecast.
Data_completion – This is the optional argument. The timeline necessitates a consistent interval between data points, is capable of accommodating up to 30% of missing data, and will automatically make the necessary adjustments.
Aggregation – This is the optional argument. The aggregation parameter is a numeric value that determines the method used to combine multiple values with the same time stamp. By default, a value of 0 will employ the AVERAGE method. However, there are alternative options available, including SUM, COUNT, COUNTA, MIN, MAX, and MEDIAN.
| Aggregation | Aggregation Method |
|---|---|
| 1 | AVERAGE |
| 2 | COUNT |
| 3 | COUNTA |
| 4 | MAX |
| 5 | MEDIAN |
| 6 | MIN |
| 7 | SUM |
How To Use FORECAST.ETS.CONFINT Function In Excel? (With Steps)
To effectively utilize the FORECAST.ETS.CONFINT function in Excel, please follow these steps:
#1 – Accessing The Function From The Excel Ribbon
Step 1: Choose an empty cell where you want the result to appear. Go to the Formulas tab on the Excel ribbon and click on it.
Step 2: From the menu, select the “More Functions” option.
Step 3: In the drop-down list, choose the “Statistical” option. Then, select “FORECAST.ETS.STAT” from the subsequent drop-down menu.
Step 4: A window named “Function Arguments” will appear. Enter the necessary values for the arguments, including value, timeline, statistical_type, data_completion, and aggregation. Once done, click on “OK”.
#2 – Entering The Function Manually In The Worksheet
Step 1: Select an empty cell where we want to find the output. Type “=FORECAST.ETS.STAT()” in the selected cell. Alternatively, you can type “=F” and then double-click on the FORECAST.ETS.CONFINT function from the list of suggestions provided by Excel.
Step 2: Press the “Enter” key to obtain the desired result.
Examples
Example #1
In this example, we will explore the concept of the FORECAST.ETS.CONFINT Excel function and its practical application. Our objective is to calculate the confidence value.
The data table presents the values for the year 2018, and we will also calculate the confidence value of 2019. Let’s create an organized table and proceed with the calculation.
To calculate the output of the FORECAST.ETS.CONFINT function in cell C14, follow these steps:
Step 1: To begin with, select the cell C14.
Step 2: The complete FORECAST.ETS.CONFINT formula is:
=FORECAST.ETS.CONFINT(A14,B2:B13,A2:A13)
Step 3: As a result, you will see the corresponding value displayed in cell C14, similar to the one shown in the provided image.
Example #2
For example, in the image below we will delve into the concept of the FORECAST.ETS.CONFINT Excel function and its practical application. Our primary objective is to calculate the confidence value.
The data table presents the values for the year 2019, and we will also determine the confidence value for 2020 sales. Below is an organized table containing the values.
To determine the output of the FORECAST.ETS.CONFINT function in cells C14 and C15, please adhere to the following steps:
Step 1: To start with, choose cell C14.
Step 2: The FORECAST.ETS.CONFINT formula is
=FORECAST.ETS.CONFINT(A14, B2:B13, A2:A13)
Step 3: Consequently, you will observe the corresponding value being showcased in cells C14 & C15, mirroring the one illustrated in the accompanying image.
Example #3
In this example, we will calculate the confidence value with confidence level percentage as 85% using the concept of the FORECAST.ETS.CONFINT Excel function.
The data table displays the values for the year 2022, and we will further ascertain the confidence value for 2023 sales. Below, you will find a meticulously organized table encompassing these values.
To determine the output of the FORECAST.ETS.CONFINT function in cells C14, C15 and C16, kindly adhere to the following steps:
Step 1: Select the cell where you intend to compute the FORECAST.ETS.CONFINT function. Let’s opt for cell C14 in this instance. Next, proceed to input the following values:
Step 2: The formula is:
=FORECAST.ETS.CONFINT(A14, B2:B13, A2:A13,85%)
Step 3: Consequently, you will notice the corresponding value being showcased in cells C14, C15 and C16, accompanied by a graph that accurately reflects the one depicted in the accompanying image.
Important Things To Note
- The #N/A error occurs when the lengths of the supplied values and timeline arrays are different.
- The #NUM! error occurs in the following situations:
- A consistent step size cannot be determined in the dates/times of the supplied timeline.
- The confidence_level value is not within the valid range of 0 – 1 (exclusive).
- The seasonality value is not within the valid range of 0 – 8784.
- The data_completion is not equal to 0 or 1.
- The aggregation value is not within the valid range of 1 – 7.
- The #VALUE! error occurs when one or more of the confidence_level value, seasonality value, data_completion value, or aggregation value arguments are non-numeric.
Frequently Asked Questions (FAQs)
The FORECAST.ETS.CONFINT Excel function proves essential in various fields where precise forecasting plays a crucial role in strategic planning and decision-making processes.
This illustrative example serves to elucidate the #NUM! error occurred by using the FORECAST.ETS.CONFINT Excel function. The tabulated data adheres to a specific format.
To employ the FORECAST.ETS.CONFINT formula, input it into cell E2, exactly as demonstrated below:
=FORECAST.ETS.SEASONALITY(D2,B2:B10,A2:A10)
We can see the result in cell E2.
In forecasting, a confidence interval is a statistical measure that quantifies the uncertainty associated with an estimated value or prediction. It provides a range within which the true value of a variable is likely to fall. The confidence interval captures both random variation in data and potential biases in the model used for forecasting. Understanding confidence intervals is crucial in forecasting as it facilitates better decision-making by accounting for uncertainties inherent in predictive analysis.
• This function requires a minimum of eight historical data points to provide reliable results. Users ensure that they have sufficient data available before using this function.
• The function calculates confidence intervals for future forecasts; it assumes that historical patterns will continue and does not account for any potential changes or unforeseen events that may impact the data significantly.
• The accuracy of the confidence intervals generated by this function can vary depending on the quality and consistency of the underlying dataset.
Download Template
This article must help us understand the FORECAST.ETS.CONFINT Excel Function’s formula and examples. You can download the template here to use it instantly.
Recommended Articles
This has been a guide to FORECAST.ETS.CONFINT Excel. Here we learn how to use FORECAST.ETS.CONFINT Function In Excel, with syntax, examples, and downloadable template. You can learn more from the following articles –
Leave a Reply