The Excel FORECAST.ETS.CONFINT function returns a confidence interval for a value forecast by FORECAST.ETS — the ± margin you add and subtract from the prediction to build an upper and lower band.
Syntax
| Argument | Description | |
|---|---|---|
target_date | Required | The future point you are forecasting — the same value passed to FORECAST.ETS. Must lie beyond the historical timeline. |
values | Required | The historical data range you want to forecast forward (e.g. monthly sales in B2:B40). |
timeline | Required | The matching range of dates or numbers (e.g. A2:A40). Must be evenly spaced and the same size as values. |
confidence_level | Optional | A number 0–1 for the interval width. Defaults to 0.95 (a 95% interval). |
seasonality | Optional | 0 = no seasonality, 1 = auto-detect (default), or a number for a fixed season length. |
data_completion | Optional | 1 (default) interpolates missing points; 0 treats gaps as zeros. |
aggregation | Optional | How duplicate timeline entries are combined (1 = AVERAGE, the default). |
How to use it
FORECAST.ETS predicts a single future value; FORECAST.ETS.CONFINT tells you how much to trust it. The result is a half-width — add it to and subtract it from the forecast to get the prediction band:
So the upper limit is forecast + confint and the lower limit is forecast − confint. A wider confidence_level (say 0.99) returns a larger margin, because a 99% interval has to be more generous than a 95% one.
Tip: the same five core arguments power the whole ETS family — FORECAST.ETS, FORECAST.ETS.CONFINT, FORECAST.ETS.SEASONALITY, and FORECAST.ETS.STAT all read the identical values and timeline. Build them once, reference them everywhere.
The timeline must be evenly spaced (daily, weekly, monthly…), though small gaps are allowed and interpolated by default. Excel's built-in Forecast Sheet tool (Data → Forecast Sheet) writes all of these formulas for you and charts the band automatically.
Try it: interactive demo
Pick a FORECAST.ETS.CONFINT example to see the formula and its result.
Practice workbook
Frequently asked questions
What does FORECAST.ETS.CONFINT actually return?
How do I change the confidence level?
0.95 (the default) gives a 95% interval; 0.99 gives a wider 99% interval; 0.80 gives a narrower one.Why do I get a #N/A or #VALUE! error?
values and timeline ranges of different sizes, or a target_date that falls before the end of the history. Confidence_level must also be strictly between 0 and 1.Which Excel versions have FORECAST.ETS.CONFINT?
Master functions like this in one day
This page covers one function. Our Excel Formulas and Functions class covers the 30 that matter most — live, hands-on, taught by professionals in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class