FORECAST.ETS.SEASONALITY Function

Returns the length of the repeating pattern Excel detects in a time series.

Category

Statistical

Available in

Excel 2016+, Microsoft 365

Returns

A whole number

Taught hands-on in Data Analytics · Relationships & Trends module

Overview

FORECAST.ETS.SEASONALITY returns the season length that FORECAST.ETS would detect automatically — for example 4 for a quarterly cycle, 12 for a yearly cycle in monthly data, or 7 for a weekly cycle in daily data. 0 means no repeating pattern was found.

Use it to check the automatic detection, or to show the detected cycle next to a forecast.

Syntax

=FORECAST.ETS.SEASONALITY(values, timeline, [data_completion], [aggregation])

Arguments

ArgumentRequired?What it does
valuesYesThe historical values.
timelineYesThe dates or period numbers, with a constant step.
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 — Detect the quarterly cycle

Three years of quarterly sales.

D2fx=FORECAST.ETS.SEASONALITY(B2:B13,A2:A13)
ABCD
1QuarterSalesSeason length
21954
32115
43135
54105
65115
76135
87155
98125
109135
1110155
1211175
1312145
=FORECAST.ETS.SEASONALITY(B2:B13,A2:A13) Explain

The pattern repeats every 4 quarters.

Common mistakes

  • #NUM! The timeline has no constant step or too many missing points.
  • #N/A values and timeline are different sizes.
  • Short histories. A cycle can only be detected when the history covers it at least twice.

Notes

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.