1
$\begingroup$

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.

$\endgroup$

2 Answers 2

1
$\begingroup$

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.

$\endgroup$
4
  • $\begingroup$ 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? $\endgroup$
    – Kim Stacks
    Jan 31, 2017 at 23:44
  • $\begingroup$ 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? $\endgroup$
    – Kim Stacks
    Feb 1, 2017 at 0:03
  • $\begingroup$ @KimStacks Yes you're right. $\endgroup$ Feb 1, 2017 at 13:18
  • $\begingroup$ @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. $\endgroup$ Feb 3, 2017 at 15:40
1
$\begingroup$

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

$\endgroup$
4
  • $\begingroup$ 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)$$ $\endgroup$
    – Kim Stacks
    Jan 31, 2017 at 23:36
  • $\begingroup$ I understood that you let $$v(i)=w(n-i)$$ but not sure how that makes $$x(j−n+i)$$ become $$x(j-i)$$ $\endgroup$
    – Kim Stacks
    Jan 31, 2017 at 23:37
  • $\begingroup$ I added some additional explanation. $\endgroup$ Jan 31, 2017 at 23:58
  • $\begingroup$ Does your final form mean the same thing as the example illustrated here math.stackexchange.com/questions/2122471/… $\endgroup$
    – Kim Stacks
    Feb 1, 2017 at 0:43

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .