# Explain weighted moving average in a way for a beginner programmer to implement it in Google Spreadsheets

I needed to implement a moving average that can handle missing data. The catch is that the number of data points to be considered should be a variable easy for me to adjust.

$$y(j)=\sum_{i=0}^nw(i)⋅x(j−n+i)$$

I need some help understanding this formula. I prefer to really grok it rather than do a copy paste job.

The best way to grok an expression like that is to write it out completely by hand (without the $\Sigma$) for some small values. Try $n=3$. Imagine weights $1/2, 1/3, 1/6$ for the three most recent values of $x$. The idea behind the weights is that what happened most recently should influence the average more.

Then calculate with some sequence of, say, ten $x$ values to find the $y$ values, starting at the third $x$ value so you have three values to sum over.

Setting this up in a spreadsheet is a separate question - one you seem to have an answer for.

• so let's say i use the weights 1/2, 1/3, 1/6, you are saying for the 3rd data point the weighted average is = 1/2(D1)+1/3(D2) +1/6(D3). Then for D4, the weighted average is 1/2(D2) + 1/3(D3)+1/6(D4). Am I right? Jan 31, 2017 at 23:44
• Sorry I meant 1/2(D3)+1/3(D2) +1/6(D1). Then for D4, the weighted average is 1/2(D4) + 1/3(D3)+1/6(D2). Am I right? Feb 1, 2017 at 0:03
• @KimStacks Yes you're right. Feb 1, 2017 at 13:18
• @KimStacks You're welcome. In your application, dealing with missing values will require a decision about how to do the weighting of the values you have. Feb 3, 2017 at 15:40

Your formula is $y(j)=\sum_{i=0}^nw(i)⋅x(j−n+i)$.

Replacing $i$ by $n-i$, the range of $i$ is still $0$ to $n$ and this becomes $y(j) =\sum_{i=0}^nw(n-i)⋅x(j−n+(n-i)) =\sum_{i=0}^nw(n-i)⋅x(j−i)$.

Finally, reversing the order of the weights by letting $v(i)=w(n-i)$, you get $y(j)=\sum_{i=0}^nv(i)⋅x(j−i)$.

Here you have the $n+1$ values ending at $j$ weighted by the $v(i)$.

• I don't even get how you go from $$y(j)=\sum_{i=0}^nw(i)⋅x(j−n+i)$$ to $$y(j)=\sum_{i=0}^nv(i)⋅x(j-i)$$ Jan 31, 2017 at 23:36
• I understood that you let $$v(i)=w(n-i)$$ but not sure how that makes $$x(j−n+i)$$ become $$x(j-i)$$ Jan 31, 2017 at 23:37