The short answer:
=AVERAGEIFS(A1:A99, B1:B99,"<="&B2, B1:B99, ">"&B2 - 30)
Values to be averaged are in A1:A99, corresponding dates are in B1:B99.
What the formula does: average the values in the range – Include a value in calculating average for the current date if:
- The date for that value is same as or before the date in the current row, and
- The date for that value is greator than the date X days before the date in the current row (X is 30 in the formula for a 30-day moving average)
The long one:
I have a spreadsheet with my daily weight log. It has occasional missing days – when I didn’t log my weight.
Yesterday, I wanted to chart this data, and wanted to add a moving average to it. Google sheets’ in-built moving average trend line refused to work – either due to the missing data, or due to the number of entries. So I added a column to the sheet with the calculated (trailing) moving average weight.
I’ve never before had to calculate moving average over a non-consecutive data set. So, in case I forget, I’m noting it down here for later…
Continue reading TIL: Moving average with missing periods in Google Sheets