FORECAST.ETS.CONFINT Function

Returns the ± margin around a FORECAST.ETS forecast.

Category

Statistical

Available in

Excel 2016+, Microsoft 365

Returns

A number

Want to practice FORECAST.ETS.CONFINT in a real spreadsheet? Open a blank sheet in our Practice Sandbox and try it with live data, with the AI Excel tutor beside you. No Excel needed — just a free account.Try it free in the Sandbox

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

=FORECAST.ETS.CONFINT(target_date, values, timeline, [confidence_level], [seasonality], [data_completion], [aggregation])

Arguments

ArgumentRequired?What it does
target_dateYesThe date or period of the forecast; must come after the last point of the timeline.
valuesYesThe historical values.
timelineYesThe dates or period numbers, with a constant step.
confidence_levelNoBetween 0 and 1; 0.95 (95%) if left out.
seasonalityNo1 (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_completionNo1 (default) fills missing points with the average of their neighbours; 0 treats them as zero.
aggregationNoHow 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.

F2fx=FORECAST.ETS.CONFINT(D2,B2:B13,A2:A13)
ABCDEF
1QuarterSalesQuarterForecast± 95%
219613154.544.50
32113
43137Lower150.04
54104Upper159.03
65117
76133
87156
98126
109134
1110157
1211173
1312147
=FORECAST.ETS.CONFINT(D2,B2:B13,A2:A13) Explain

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.

=FORECAST.ETS.CONFINT(D2,B2:B13,A2:A13,0.99) Explain

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.
Kyle Stott

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.