FORECAST.ETS function
FORECAST.ETS projects a future value from a time series, allowing for trend and repeating seasonal patterns. It suits sales or demand forecasting and needs a timeline with a consistent interval.
Example
Excel shows 125
AE
AF
1
=DATE(2023,1,1)
=100+10*MOD(1-1,4)+1
2
=DATE(2023,2,1)
=100+10*MOD(2-1,4)+2
3
=DATE(2023,3,1)
=100+10*MOD(3-1,4)+3
4
=DATE(2023,4,1)
=100+10*MOD(4-1,4)+4
5
=DATE(2023,5,1)
=100+10*MOD(5-1,4)+5
6
=DATE(2023,6,1)
=100+10*MOD(6-1,4)+6
7
=DATE(2023,7,1)
=100+10*MOD(7-1,4)+7
8
=DATE(2023,8,1)
=100+10*MOD(8-1,4)+8
9
=DATE(2023,9,1)
=100+10*MOD(9-1,4)+9
10
=DATE(2023,10,1)
=100+10*MOD(10-1,4)+10
11
=DATE(2023,11,1)
=100+10*MOD(11-1,4)+11
12
=DATE(2023,12,1)
=100+10*MOD(12-1,4)+12
13
=DATE(2023,13,1)
=100+10*MOD(13-1,4)+13
14
=DATE(2023,14,1)
=100+10*MOD(14-1,4)+14
15
=DATE(2023,15,1)
=100+10*MOD(15-1,4)+15
16
=DATE(2023,16,1)
=100+10*MOD(16-1,4)+16
17
=DATE(2023,17,1)
=100+10*MOD(17-1,4)+17
18
=DATE(2023,18,1)
=100+10*MOD(18-1,4)+18
19
=DATE(2023,19,1)
=100+10*MOD(19-1,4)+19
20
=DATE(2023,20,1)
=100+10*MOD(20-1,4)+20
21
=DATE(2023,21,1)
=100+10*MOD(21-1,4)+21
22
=DATE(2023,22,1)
=100+10*MOD(22-1,4)+22
23
=DATE(2023,23,1)
=100+10*MOD(23-1,4)+23
24
=DATE(2023,24,1)
=100+10*MOD(24-1,4)+24
Data
Syntax
FORECAST.ETS(target_date, values, timeline, [seasonality], [data_completion], [aggregation])
Arguments in [square brackets] are optional.
-
target_date(required) - The date or number to forecast for.
-
values(required) - Historical values.
-
timeline(required) - Dates or numbers with a constant step.
-
seasonality(optional) - 1 auto-detect (default), 0 none, or a season length.
-
data_completion(optional) - 1 fills gaps by interpolation (default), 0 treats gaps as zero.
-
aggregation(optional) - How to combine duplicate timestamps; 1 is average.
Returns: A forecast value from exponential triple smoothing.
What Excel actually returns
Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.
| Formula | Excel shows | Case |
|---|---|---|
=FORECAST.ETS(4,{1,2},{1,2}) | 4 | Too few points |
=FORECAST.ETS(DATE(2025,1,1),Data!AF1:AF24,Data!AE1:AE24,1) | 125 | Working example |
=FORECAST.ETS(DATE(2025,1,1),Data!AF1:AF24,Data!AE1:AE24) | 125 | Auto seasonality |
=FORECAST.ETS(DATE(2025,1,1),Data!AF1:AF24,Data!AE1:AE24,0) | 144.7038525 | No seasonality |
=FORECAST.ETS(DATE(2025,1,1),Data!AF1:AF24,Data!AE1:AE24,4) | 125 | Explicit seasonality 4 |
=FORECAST.ETS(DATE(2023,6,1),Data!AF1:AF24,Data!AE1:AE24) | 116 | Target inside history |
=FORECAST.ETS(DATE(2022,1,1),Data!AF1:AF24,Data!AE1:AE24) | #NUM! | Target before start |
=FORECAST.ETS(DATE(2025,1,1),Data!AF1:AF23,Data!AE1:AE24) | #N/A | Different sizes |
The Data sheet these formulas read
A cell that starts with = holds that formula.
AE
AF
1
=DATE(2023,1,1)
=100+10*MOD(1-1,4)+1
2
=DATE(2023,2,1)
=100+10*MOD(2-1,4)+2
3
=DATE(2023,3,1)
=100+10*MOD(3-1,4)+3
4
=DATE(2023,4,1)
=100+10*MOD(4-1,4)+4
5
=DATE(2023,5,1)
=100+10*MOD(5-1,4)+5
6
=DATE(2023,6,1)
=100+10*MOD(6-1,4)+6
7
=DATE(2023,7,1)
=100+10*MOD(7-1,4)+7
8
=DATE(2023,8,1)
=100+10*MOD(8-1,4)+8
9
=DATE(2023,9,1)
=100+10*MOD(9-1,4)+9
10
=DATE(2023,10,1)
=100+10*MOD(10-1,4)+10
11
=DATE(2023,11,1)
=100+10*MOD(11-1,4)+11
12
=DATE(2023,12,1)
=100+10*MOD(12-1,4)+12
13
=DATE(2023,13,1)
=100+10*MOD(13-1,4)+13
14
=DATE(2023,14,1)
=100+10*MOD(14-1,4)+14
15
=DATE(2023,15,1)
=100+10*MOD(15-1,4)+15
16
=DATE(2023,16,1)
=100+10*MOD(16-1,4)+16
17
=DATE(2023,17,1)
=100+10*MOD(17-1,4)+17
18
=DATE(2023,18,1)
=100+10*MOD(18-1,4)+18
19
=DATE(2023,19,1)
=100+10*MOD(19-1,4)+19
20
=DATE(2023,20,1)
=100+10*MOD(20-1,4)+20
21
=DATE(2023,21,1)
=100+10*MOD(21-1,4)+21
22
=DATE(2023,22,1)
=100+10*MOD(22-1,4)+22
23
=DATE(2023,23,1)
=100+10*MOD(23-1,4)+23
24
=DATE(2023,24,1)
=100+10*MOD(24-1,4)+24
Data
Watch out for
- Timeline needs a consistent interval.
- Fails on duplicate or unsorted timestamps unless aggregation handles them.
- Results come from an algorithm, so hand-checking is hard.
Where it works
- Excel
- FORECAST.ETS arrived in Excel 2016. Older versions do not have it. Reported as unavailable in some Excel for Mac versions; check your build.
- Google Sheets
- No direct equivalent. Sheets has no equivalent; FORECAST or TREND give only straight-line forecasts.
Part of Statistical functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .