The `FORECAST.ETS.CONFINT` function in Excel is used to calculate the confidence interval for a forecasted value based on an exponential smoothing algorithm. This is particularly useful when you’re using the `FORECAST.ETS` function to make predictions over a time series, and you want to understand the potential variability in those predictions.
Here’s how you can use the `FORECAST.ETS.CONFINT` function:
Syntax
FORECAST.ETS.CONFINT(target_date, values, timeline, [confidence_level], [seasonality], [data_completion], [aggregation])
Arguments
- `target_date`: (Required) The data point for which you want to predict the confidence interval. The target date can be a date/time value or a numerical value.
- `values`: (Required) The historical values you want to base your forecast on. This should be a range or an array of dependent values.
- `timeline`: (Required) The array or range of independent values. The timeline needs to be consistent with the `values` range and chronologically ordered.
- `confidence_level`: (Optional) A numerical value between 0 and 1 that represents the confidence level for the interval. The default is 0.95, indicating a 95% confidence interval.
- `seasonality`: (Optional) An integer value that represents the length of the seasonal pattern. Use `1` for no seasonality, or provide a specific positive integer to define the periodic pattern. Use `0` for automatic detection.
- `data_completion`: (Optional) A numerical value (0 or 1) indicating how to handle missing data. Use `1` (default) for automatic completion or `0` to account only for existing data points.
- `aggregation`: (Optional) A numerical value indicating the aggregation function to be used if multiple values have the same timestamp. Options include `1` for AVERAGE, `2` for COUNT, `3` for COUNTA, `4` for MAX, `5` for MIN, `6` for MEDIAN, `7` for SUM.
Example
Suppose you have a timeline of dates in column A and sales data in column B, and you want to project data for the date in A15. Here’s how you could use `FORECAST.ETS.CONFINT`:
=FORECAST.ETS.CONFINT(A15, B2:B14, A2:A14, 0.95, 1, 1, 1)
- `A15`: The target date for which you’re forecasting.
- `B2:B14`: The range of historical sales values.
- `A2:A14`: The range of the corresponding timeline dates.
- `0.95`: Set to a 95% confidence level (optional parameter).
- `1`: No seasonality (as an example).
- `1`: Automatically handle missing data.
- `1`: Aggregate by average if needed.
Notes
- Ensure your timeline is continuous and does not contain duplicates.
- This function is available in Excel 2016 and later versions.
- The confidence interval helps you understand the reliability of the forecasted value.
By using the `FORECAST.ETS.CONFINT` function, you can better quantify the uncertainty associated with your forecasts and convey that variability to stakeholders.
Unlock Your Potential
Excel
Access
Power BI
