A weighted moving average gives recent points more pull than older ones — smoothing a series while staying responsive. SUMPRODUCT multiplies each value by its weight and divides by the weight total.
The example
Newest reading weighted heaviest.
| A | B | |
|---|---|---|
| 1 | Value | Weight |
| 2 | 10 | 1 |
| 3 | 20 | 2 |
| 4 | 30 | 3 → WMA 23.3 |
The formula
The formula:
How it works
How it works:
SUMPRODUCT(values, weights)multiplies each value by its weight and adds the products.- Dividing by
SUM(weights)normalizes the result to a proper average. - Give the newest point the largest weight (e.g. 3, 2, 1) so it responds faster than a plain moving average.
- The weights can be anything — linear (3,2,1), exponential, or custom by importance.
Linear vs exponential: linear weights (3,2,1) are simple and intuitive; exponential weighting (each older point a fixed fraction of the last) reacts even faster and is the basis of EWMA used in finance and control charts. SUMPRODUCT handles any weighting scheme.
Try it: interactive demo
Values and weights.
Variations
Plain moving average
Equal weights:
Weights sum to 1
Pre-normalized:
Last 3, recency-weighted
3-2-1:
Pitfalls & errors
Always divide by SUM(weights). Skip it and you get a weighted total, not an average.
Match the lengths. Values and weights ranges must be the same size or SUMPRODUCT errors.
Order the weights. Make sure the biggest weight lines up with the newest value.
Practice workbook
Frequently asked questions
How do I calculate a weighted moving average in Excel?
Why divide by SUM(weights)?
What weights should I use?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class