FORECAST.ETS.CONFINT Function
Returns the ± margin around a FORECAST.ETS forecast.
Category
Statistical
Available in
Excel 2016+, Microsoft 365
Returns
A number
Overview
FORECAST.ETS.CONFINT returns the half-width of a confidence interval around the FORECAST.ETS forecast for the same date. Subtract it for the lower bound and add it for the upper bound.
At the default 95% confidence, the actual value should land inside the range about 95 times in 100. The margin grows the further ahead you forecast, and the noisier the history.
The timeline must be numbers or dates with a constant step (every day, every 7 days, the 1st of every month …). It doesn't need to be sorted, and up to 30% of the points can be missing; data_completion decides whether gaps are filled by averaging neighbours (1, the default) or treated as zero (0). Duplicate dates are combined with the aggregation method (default AVERAGE).
Syntax
Arguments
| Argument | Required? | What it does |
|---|---|---|
| target_date | Yes | The date or period of the forecast; must come after the last point of the timeline. |
| values | Yes | The historical values. |
| timeline | Yes | The dates or period numbers, with a constant step. |
| confidence_level | No | Between 0 and 1; 0.95 (95%) if left out. |
| seasonality | No | 1 (default) detects the season length automatically, 0 means no seasonality, and a whole number up to 8,760 sets the length yourself (4 for quarters, 12 for months). |
| data_completion | No | 1 (default) fills missing points with the average of their neighbours; 0 treats them as zero. |
| aggregation | No | How to combine values with the same date: 1 AVERAGE (default), 2 COUNT, 3 COUNTA, 4 MAX, 5 MEDIAN, 6 MIN, 7 SUM. |
Examples
Example 1 — A forecast range for quarter 13
Quarterly sales with everyday ups and downs on top of the seasonal pattern.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Quarter | Sales | Quarter | Forecast | ± 95% | |
| 2 | 1 | 96 | 13 | 154.54 | 4.50 | |
| 3 | 2 | 113 | ||||
| 4 | 3 | 137 | Lower | 150.04 | ||
| 5 | 4 | 104 | Upper | 159.03 | ||
| 6 | 5 | 117 | ||||
| 7 | 6 | 133 | ||||
| 8 | 7 | 156 | ||||
| 9 | 8 | 126 | ||||
| 10 | 9 | 134 | ||||
| 11 | 10 | 157 | ||||
| 12 | 11 | 173 | ||||
| 13 | 12 | 147 |
Plan for roughly 150 to 159, not just the single figure of 154.5.
Example 2 — A wider, 99% range
Higher confidence means a wider margin.
Common mistakes
- #NUM! confidence_level isn't between 0 and 1, or the timeline problems that also break FORECAST.ETS.
- Use the same settings. Give CONFINT the same seasonality, data_completion and aggregation as the matching FORECAST.ETS, or the range won't fit the forecast.
Notes
- Excel tunes its smoothing constants with its own optimiser, so figures from desktop Excel can differ slightly from the Sandbox on noisy data.
- Related functions: FORECAST.ETS, FORECAST.ETS.STAT, CONFIDENCE.T.

Author
Kyle Stott
Certified Microsoft Excel Expert
Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.