Abstract
A moving average (MA) process, also called weighted moving average (WMA) is a type of signal filtering that consists in performing a weighted average over a finite past sequence of samples of the original signal. Implementing such scheme in a worksheet is not always straightforward due to the handling of missing values. This article proposes a convenient way to implement such filters on Google Sheets.Keywords
Weighted Moving Average, MA, WMA, Noise Removal, Filtering, Google Sheets, Spreadsheets.Introduction
The moving average signal is defined by:\[y(j) = \sum_{i=0}^n w(i) \cdot x(j-n+i)\]
The spreadsheet function \(AVERAGE()\) performs this computation in the particular case where all \(w(i)\) are equal to one. In this case, it is very easy to deal with missing values, as the only thing you need to do is count the number of non-missing values and divide the sum by this count.
When the \(w(i)\)'s are different numbers, it is not enough to count the number of non-missing values, one needs to perform the final normalization using the sum of the correspondent weight coefficients.
Proposed Solution
The proposed solution is to use the function \(SUMPRODUCT()\) twice, first to get the weighted sum, and second to get the sum of the coefficients that have multiplied non-missing data.Assume that:
- The spreadsheet has a page called "Filter1".
- The averaging coefficients are on column Filter1!A.
- Cell Filter1!B1 has the formula "COUNT(A:A)", which will count the number of averaging coefficients.
- The current cell is B22.
\[\begin{array}{l}
=\\
SUMPRODUCT( \\
\qquad OFFSET(B22, -Filter1!$B$1 + 1, 0, Filter1!$B$1, 1), \\
\qquad OFFSET(Filter1!$A$1, 0, 0, Filter1!$B$1, 1)) \\
/ \\
SUMPRODUCT(\\
\qquad ARRAYFORMULA(\\
\qquad \qquad N(ISNUMBER(\\
\qquad \qquad \qquad OFFSET(B22, -Filter1!$B$1 + 1, 0, Filter1!$B$1, 1)))), \\
\qquad OFFSET(Filter1!$A$1, 0, 0, Filter1!$B$1, 1))
\end {array}\]
The details of this expression are as follows:
- \(Filter1!$B$1\) is \(n\).
- \(OFFSET(current\_cell,−n+1,0,n,1)\) is used to produce a range of n cells, of which the current cell is the last one.
- \(OFFSET(Filter1!$A$1, 0, 0, Filter1!$B$1, 1)\) are the weighting coefficients.
- \(ARRAYFORMULA(N(ISNUMBER(OFFSET(\cdots))))\) applies the function \(N()\) to the boolean result of \(ISNUMBER()\) for each cell in the current range, which will produce an array of zeroes where there is missing data and ones where there is data.
This comment has been removed by a blog administrator.
ReplyDelete