Ravindra BagaleCourses & study guides मराठी Track your progress

31. Forecasting in Excel

31.6 Seasonality by Hand: Trend plus Weekday Offsets with TREND and AVERAGEIFS

By Last updated:

Example first. The operations head likes the ETS forecast but asks: "Why is Saturday 103 orders higher than Tuesday? Show me." ETS hides its pattern inside the formula. Salman rebuilds the forecast in four visible columns: the straight-line trend, the distance of each day from the trend, the average distance per weekday (the weekday offset: Saturday +55.3, Tuesday -40.3), and finally trend + offset for next week. Every number can be pointed at. This is the classic "decomposition" method, built only with TREND, WEEKDAY and AVERAGEIFS.

Trend plus weekday offsets — in 30 seconds

What: A seasonal forecast you can see: fit a straight-line trend, subtract it to get the detrended values, average them by weekday to get seven offsets, then forecast = future trend + that weekday's offset.

Why: It explains the seasonal pattern in plain numbers ("Saturdays are 55 orders above trend"), works in every Excel version including the web, and is a good check on FORECAST.ETS.

Tiny example: Trend for Monday 28 Sep: =TREND($B$2:$B$57,$C$2:$C$57,57) = 490.99. Monday offset −29.27. Forecast = 461.72, about 462 orders.

First look at the data (before → after):

Trend plus weekday offsets: before and after tables, fictional Blinkit Maharashtra data

Expected result — Weekday offsets

Weekday Offset
Mon -29.27
Tue -40.26
Wed -24.00
Thu -9.98
Fri 20.16
Sat 55.29
Sun 28.06
Before table and formula as text

Before — Daily data with helper columns

A Date B Orders C t F Weekday
Mon 3 Aug 373 1 1
Tue 4 Aug 356 2 2
… … … …
Sun 27 Sep 516 56 7

Formula / click path

C2: =ROW()-1   D2: =TREND($B$2:$B$57,$C$2:$C$57,C2)   E2: =B2-D2   F2: =WEEKDAY(A2,2)
I2: =AVERAGEIFS($E$2:$E$57,$F$2:$F$57,H2)   (H2:H8 = 1 to 7)
Forecast: =TREND($B$2:$B$57,$C$2:$C$57,57)+XLOOKUP(WEEKDAY(A58,2),$H$2:$H$8,$I$2:$I$8)

Do it step by step:

  1. In C2 type =ROW()-1 and fill to C57 to number the days 1 to 56.
  2. In D2 type =TREND($B$2:$B$57,$C$2:$C$57,C2) (the trend for that day) and in E2 =B2-D2 (how far the day is from the trend). Fill both down.
  3. In F2 type =WEEKDAY(A2,2) and fill down: 1 = Monday … 7 = Sunday.
  4. Type 1 to 7 in H2:H8. In I2 type =AVERAGEIFS($E$2:$E$57,$F$2:$F$57,H2) and fill to I8. These are the seven weekday offsets.
  5. In C58 type =TREND($B$2:$B$57,$C$2:$C$57,57)+XLOOKUP(WEEKDAY(A58,2),$H$2:$H$8,$I$2:$I$8) for Monday 28 September (461.72). Change 57 to 58, 59… for the next days.
  6. Compare with FORECAST.ETS from lesson 31.3: the two methods should tell the same story.

The seven offsets (average distance from the trend, by weekday)

WEEKDAY(…,2) Day Offset (orders)
1 Mon -29.27
2 Tue -40.26
3 Wed -24.00
4 Thu -9.98
5 Fri 20.16
6 Sat 55.29
7 Sun 28.06

The offsets add up to about 0 (0.00), because the trend line already passes through the middle of the data. Saturday is about 55 orders above the trend and Tuesday about 40 below it. That is the weekly pattern in one small table you can show to anyone.

Next week: trend + offset, next to ETS

Date Trend (TREND) Offset Trend + offset FORECAST.ETS (approximate)
Mon 28 Sep 490.99 -29.27 461.7 ≈ 455
Tue 29 Sep 492.73 -40.26 452.5 ≈ 445
Wed 30 Sep 494.46 -24.00 470.5 ≈ 463
Thu 1 Oct 496.20 -9.98 486.2 ≈ 479
Fri 2 Oct 497.94 20.16 518.1 ≈ 511
Sat 3 Oct 499.67 55.29 555.0 ≈ 548
Sun 4 Oct 501.41 28.06 529.5 ≈ 522

Exact values on this page were computed with Excel's documented formulas and re-checked in LibreOffice Calc 25.2 with the same formulas.

The trend line is Orders = 391.98 + 1.74 × t, so each day adds about 1.74 orders. Both methods agree on the shape (Saturday highest, Tuesday lowest). The trend-plus-offset forecast is about 7 orders higher than ETS on every day, because the straight trend gives the same weight to all 8 weeks, while ETS follows the most recent weeks more closely. Lesson 31.7 tests which one was more accurate on a held-back week.

Additive or multiplicative? Offsets in orders (+55 on Saturday) assume the weekly swing stays the same size as the business grows. If the swing grows with the level (Saturday is always about 12% above the trend), use a ratio instead: =B2/D2 in column E and forecast = trend × ratio. Excel's FORECAST.ETS uses the additive version (AAA).

Two quick seasonal baselines

  • Same day last week (seasonal naive): forecast for Monday 28 September = the actual of Monday 21 September (448). In C58: =B51. It is crude but often hard to beat over one week.
  • Average of the same weekday over the last 4 weeks: =AVERAGE(B30,B37,B44,B51) for Monday = 428.50. Safer than one week, but it ignores the trend.

Why build this when ETS exists? It works in every Excel, including Excel for the web where FORECAST.ETS is missing. It explains the pattern in a table a manager understands, and when it and ETS disagree strongly, something in the data needs a look.

Microsoft sources (checked 7 October 2026): TREND function.

Practice task (try it before you open the answer):

  1. What is the forecast for Saturday 3 October with trend + offset? Write the formula.
  2. Which weekday has an offset closest to 0, and what does that mean?
  3. The seasonal-naive forecast for Saturday 3 October is which cell, and what value?
Answer
  1. =TREND($B$2:$B$57,$C$2:$C$57,62)+XLOOKUP(WEEKDAY(A63,2),$H$2:$H$8,$I$2:$I$8) = 499.67 + 55.29 ≈ 554.97 (the cell keeps all decimals, so it is not exactly the sum of the two rounded numbers), about 555 orders.
  2. Thursday (-9.98): Thursdays are close to the trend, neither busy nor quiet.
  3. =B56, the actual of Saturday 26 September: 536 orders.

Ravindra Bagale's Tip

Put the seven weekday offsets on the first page of any forecast deck. "Saturday +55, Tuesday −40" explains a staffing plan better than any chart.

Loading practice tasks...

  1. Average covers (Easy): Average of C2:C15, rounded to 1 decimal.
  2. Saturday average (Easy): Average covers where Weekday is "Sat".
  3. Monday average (Easy): Average covers where Weekday is "Mon".
  4. Saturday offset (Medium): Saturday average minus overall average, rounded to 1 decimal.
  5. Daily trend (Medium): Slope of C2:C15 on A2:A15, rounded to 3 decimals.
  6. Trend value day 20 (Medium): Forecast for day 20, rounded to 1 decimal.
  7. Day 20 (Sat) forecast (Hard): Forecast for day 20 + Saturday offset, rounded to 0 decimals.
  8. Day 16 (Tue) forecast (Hard): Forecast for day 16 + Tuesday offset, rounded to 0 decimals.
  9. Weekend share (Hard): Covers on Fri, Sat, Sun ÷ all covers, rounded to 3 decimals.
  10. Seasonal index Fri (Hard): Friday average ÷ overall average, rounded to 3 decimals.

See all practice tasks in the Task Hub →