(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:
where 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.
Information Entropy 
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.
Thanks!
Thank you, I was struggling with creating right formula.
Thank you for this formula.
If anyone gets Error 503 trying to use it, it’s because the author chose to use weird non-ASCII double quote characters in the formula. Replace them with character: “
OK it seems the blog software is converting normal, ASCII double quotes to weird Latin1 or whatever double quote characters, even in my comment.