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

## 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.
Then the claim is that the following formula will calculate the correct value of WMA:
$\begin{array}{l} =\\ SUMPRODUCT( \\ \qquad OFFSET(B22, -Filter1!B1 + 1, 0, Filter1!B1, 1), \\ \qquad OFFSET(Filter1!A1, 0, 0, Filter1!B1, 1)) \\ / \\ SUMPRODUCT(\\ \qquad ARRAYFORMULA(\\ \qquad \qquad N(ISNUMBER(\\ \qquad \qquad \qquad OFFSET(B22, -Filter1!B1 + 1, 0, Filter1!B1, 1)))), \\ \qquad OFFSET(Filter1!A1, 0, 0, Filter1!B1, 1)) \end {array}$
The details of this expression are as follows:
• $$Filter1!B1$$ 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!A1, 0, 0, Filter1!B1, 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.

## Results

The formula has been tested against some weighting data. The resulting spreadsheet has a plot of the original data, along with the $$AVERAGE()$$ data and WMA data for comparison.

## Conclusion

A spreadsheet formula for the correct calculation of a weighted moving average has been derived and successfully tested on Google Sheets. The proposed formula deals with missing values in a way similar to the $$AVERAGE()$$ function, avoiding the distortions that would be caused either by using zero in place of the missing values or by packing the original series.