Statistical
Weighted Moving Average (WMA) formula: emphasize recent demand
React faster to trend turns than a simple moving average by weighting trailing observations linearly {1;2;3} inside SUMPRODUCT.
Why linear weights {1;2;3} reduce moving-average lag
A 3-week Simple Moving Average (SMA) gives Week t-2, Week t-1, and Week t equal 33.3% weight—so a sharp jump in the latest week is diluted by two stale weeks.
A 3-period Weighted Moving Average assigns weights 1, 2, and 3 (summing to 6): 1/6 (16.7%) to the oldest week, 2/6 (33.3%) to the middle week, and 3/6 (50.0%) to the most recent week.
| Week (A) | Demand (B) | 3-Wk Weight | SMA vs WMA at Wk 4 |
|---|---|---|---|
| Wk 1 | 100 | (outside 3-wk window) | — |
| Wk 2 | 110 | 1 (1/6) | — |
| Wk 3 | 130 | 2 (2/6) | — |
| Wk 4 | 190 | 3 (3/6) | SMA = 143.33 vs WMA = 156.67 |
3-period Weighted Moving Average vs SMA
Use semicolon row separators {1;2;3} so the inline weight vector aligns vertically with B3:B5.
=ROUND(SUMPRODUCT(B3:B5, {1;2;3}) / SUM({1;2;3}), 2)Compare with the simple moving average
AVERAGE gives each of the three observations equal weight, unlike the 1:2:3 weighted formula.
=ROUND(AVERAGE(B3:B5), 2)Inputs and interpretation
Rows run from oldest to newest with complete numeric observations at evenly spaced intervals. Weights must align with that order. Greater responsiveness can also amplify noise and does not guarantee a better forecast. Array separators vary with spreadsheet locale.
Try the example
Use the original values or change them to test your understanding.