The delivery counts are 6, 12, 9, and 15 over four consecutive weeks. A trailing three-week average first becomes available on week 3.

Fill a fixed-width window down one row

Enter the counts in B2:B5. In C4 enter =AVERAGE(B2:B4) and expect 9. Fill down to C5. The reference should shift to B3:B5, dropping 6 and adding 15, so the result is 12.

The three-week window shifts from weeks 1–3 to weeks 2–4.

Illustrated window movement. One value leaves as a new one enters.

Flag missing measurements before averaging

Keep an unreported week’s row in the sheet. If the report needs three numeric observations, use =IF(COUNT(B2:B4)=3,AVERAGE(B2:B4),"Missing week") in C4 and fill down. Leave a genuine zero as a number. Label the result Trailing 3-week average.

A missing week is flagged while zero remains a valid measurement.

Illustrated input check. Retain one row for each calendar week.

Microsoft’s reference for calculating an average