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

Question

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.

See https://superuser.com/questions/1173550/moving-average-in-google-spread-sheets-but-allowing-missing-data for details.

I have googled and came across this formula in http://eeandcs.blogspot.sg/2014/09/weighted-moving-averages-on-google.html

$$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.

Answer 1

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.

Answer 2

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)$.