Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to calculate a weighted moving average in Excel and Google Sheets
Topic:AVERAGE & central tendencyExcel & Google Sheets
=SUMPRODUCT(A2:A4,B2:B4)/SUM(B2:B4)✓
Verified example
Computed by a real spreadsheet engine on the sample data below.
| Value | Weight |
| 120 | 1 |
| 135 | 2 |
| 142 | 3 |
=SUMPRODUCT(A2:A4,B2:B4)/SUM(B2:B4)→136
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Put your time-series values in A2:A10, oldest first. Put the weights in B2:B4 — for a 3-period WMA with linear emphasis on recency, use 1, 2, 3 (weight 3 applies to the most recent value).
- 2In C4 (the first row with a full 3-period window) enter =SUMPRODUCT(A2:A4,B2:B4)/SUM(B2:B4). This computes (A2*1 + A3*2 + A4*3) / 6.
- 3For C5 shift the data window: =SUMPRODUCT(A3:A5,$B$2:$B$4)/SUM($B$2:$B$4). Lock the weight range absolutely so it stays fixed while the data range shifts one row.
- 4To make the formula self-adjusting, use OFFSET: =SUMPRODUCT(OFFSET(A4,-2,0,3,1),$B$2:$B$4)/SUM($B$2:$B$4) — copy this to any row without editing.
- 5Leave C2:C3 blank or as N/A, since there are not enough prior values to fill the window.
Tips
- Weights do not need to sum to 1 — dividing by SUM(weights) normalizes automatically. Use 1,2,3 or 10,20,30 or 0.1,0.2,0.7; the result is the same.
- For an exponential weighted average, compute weights as alpha*(1-alpha)^(window-k) in a helper column, then use the same SUMPRODUCT pattern.
- A simple moving average is AVERAGE(window) — the weighted version is worth the extra complexity only when emphasizing recency is a deliberate modeling choice.
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
How is a weighted moving average different from an exponential moving average?
A WMA uses explicit fixed weights you define, making it transparent and simple. An EMA applies a smoothing factor recursively, incorporating all past data with exponentially decaying influence — useful for financial time series.
Can I use SUMPRODUCT with weights stored as an array literal instead of a range?
Yes — hardcode the weights as an array: =SUMPRODUCT(A2:A4,{1,2,3})/6. This works in both Excel and Sheets but is less flexible when you want to change weights later.
More on AVERAGE & central tendency
See all →How-to
How to calculate an average
Use the AVERAGE function to calculate the arithmetic mean of a range of numbers in Excel or Google Sheets.
How-toHow to average ignoring zeros
Use AVERAGEIF with a "<>0" criterion to calculate an average that excludes zero values from the calculation.
How-toHow to average ignoring blank cells
AVERAGE naturally ignores blank cells; use AVERAGEIF with "<>" to also ignore text placeholders like dashes.
How-toHow to average with a condition
Use AVERAGEIF to compute the mean of values in one column where a corresponding column meets a specified criterion.
How-toHow to calculate a moving average
Compute a rolling average for each row using AVERAGE with a fixed-window OFFSET or INDEX formula anchored to each row.
How-toHow to find the median of a range
Use the MEDIAN function to find the middle value in a dataset — the value that half the data falls above and half below.