Moving Average in LibreOffice Calc

(I googled a bit and I didn’t find an answer this question, so here it is.)

How do you calculate a moving average of a value over a date range with an irregular sample interval in LibreOffice Calc?

OR, given a fictitious data set that looks like this:

Figure 1where the sample interval is irregular, how do you calculate the a moving average with only the values within your moving average range?

The answer is a combination of LibreOffice Calc’s SUMIF and COUNTIF functions.  In short, you use the SUMIF function to sum the values in column B, if the date in a selected range in column A is more recent than the number of days used by the moving average. The formula for the last value (C13) in  this spreadsheet is :

=SUMIF(A9:A13,”>=”&A13-$C$1,B9:B13)/COUNTIF(A9:A13,”>=”&A13-$C$1)

Going from left to right, the formula sums over the range from B9 to B13 (5 cells), if the corresponding row values in A9 to A13 (5 cells) are less-than-or-equal to (“>=”) the text translation of (&) what is in cell A13 (a date) minus the value in C1 (a “3” meaning the date three days previous.)  The only black magic in that part of the formula is the ampersand (&), which does the cell-to-string translation for use in the comparison function. After the division sign is the same syntax, but instead of summing the values, COUNTIF, counts them. A sum divided by the number summed is an average, so this meets the requirements.

For the case in Row 10, where none of the previous three cells are within the date range, the moving average is just the same as the value in B10.

Note that the range scanned in the dates is larger (5) than the range of the moving average (3). This is so that the range of the moving average can easily be changed to 2 or 4 or 5 to see if there is a particular value that is better than the others. Make sure that the range checked in SUMIF and COUNTIF is larger than the value used for the moving average, or else the moving average will be wrong.

UPDATE: thanks to comment from Anthony, corrected the operator in the calculation.

Advertisements
This entry was posted in Statistics, Tutorial and tagged , , , , , , , , , . Bookmark the permalink.

3 Responses to Moving Average in LibreOffice Calc

  1. Anthony says:

    Hi,
    The operators should be the same in both the SUMIF and COUNTIF functions if you want meaningful results. i.e.: if you want the Moving Average Length cell to define the (maximum) number of data points in the range then the operator for both functions should be ”>”&A13-$C$1
    HTH,
    Anthony.

  2. Michal says:

    Thank you, I was struggling with creating right formula.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s