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.
Need it for your exact data?
Describe your columns in plain English and get the precise formula for your sheet, with the right Excel or Sheets syntax.
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.