Computing Weighted Moving Average in Excel

Question

I have data points from irregular time intervals

I need to find the weighted moving average (WMA) for each these data points based on the last k records. Specifically, when computing the WMA for point p, value corresponding to pth record should be assigned a weight of 1 and the weight assigned to each of the k previous points should be inversely proportional to date difference between those points and the pth point. The older a historic record, the lesser the weight assigned to value of that record.

How do I achieve this in Excel?

From my understanding, the key piece I am missing is the ability to transform cell ranges. If theres a way to do this, I could just compute the reciprocal of date diffs and use that as weight.

Data Example

Here is an example illustrating the required computations for just one data point (the one corresponding to 11-Feb-21). This needs to be repeated for all the data points.

Formulae used:

Weight = 1/(1+ Date diff between 11-Feb-21 and data of current data point)

Weighted Value = Value * Weight

Weighted Average = Sum of Weighted Values/Sum of Weights

Could you show a typical calculation with actual figures plz? I don't think it would be too hard in principle, but a fairly obvious issue would be that if you included the current point in the moving average the date difference would be zero and the reciprocal of that (which you'd need to work out the moving average weighted in inverse proportion to date difference) would be infinity. — Tom Sharpe
@TomSharpe I will add an example shortly to clarify...I am planning to add 1 to the date diff to address the infinity situation you mentioned...my main problem is different though...had this been needed for just one data point, I could have computed intermediate values in another column and used that...but here I need those computed for every single data point — Aadith Ramia
OK, great. Also, what do you do for the first k rows where you haven't got enough previous points to work out the average? I expect you'll cover that in your example. — Tom Sharpe
@Jsmart523 my original thought was to include all the past data points in each case...but as I think through it, this can be relaxed (ie, k can be made constant) if that will make things any easier — Aadith Ramia

JSmart523 JSmart523 · Accepted Answer · 2021-03-23T21:54:41

Does this solve your problem?

Assuming 1-Jan-21 is in cell A2,

(Value * Weight) D2 = B2 * C2
(Running sum of Value * Weight) E2 = IF(ROW()=2,0,E1)+D2
(Running sum of Weight) F2 = IF(ROW()=2,0,F1)+B2
(WMA) G2 = E2/F2

Now drag the formulas for D:G down your sheet and it should give you your weighted averages.

Better yet would be to use tables. If you highlight your dataset and click Insert - Table and add new columns by changing D1 to "VW" and E1 to "RSVW", then

in D2 you'd want a formula of =[@Value]*[@[Day of Year]] (the square brackets mean we're talking about this table, the "@" symbol specifies the current row, and "Day of Year" has extra square brackets because there's a space in the column name.)
in E2 you'd want a formula of =IF(ROW()=2,0,OFFSET([@RSVW],-1,0))+[@VW] because, as long as you aren't making relative cell references by cell addresses like "E1" then the formula won't auto-fill down the column right, so instead we get the same value by using OFFSET to reference "one above the current row's RSVW value"
yadda yadda

Once you've done that, you're storing those formulas once per column instead of duplicated in each cell. As long as you don't put absolute values in those columns you can adjust/change them without having to drag your formula down the column, and you can rename the column titles. Also, I'm not sure if the first way will break when sorting but you use the table formulas then even deleting a row from the middle of your table won't break it.

Computing Weighted Moving Average in Excel

2 Answers