Site icon Converge

TIL: Moving average with missing periods in Google Sheets

Aditya

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:

  1. The date for that value is same as or before the date in the current row, and
  2. 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.

My weight and the 30-day moving average

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…

The table was setup as below (please don’t judge me on that weight – I’m struggling!)

The sheet with data entries

The formula used was:

The moving average formula

Broken down into bits, the formula is:

The moving average formula – parts

‘averageifs’ provides the average of a cells in range where all corresponding conditions are met. The inputs to the formula are:

  1. The range with values to average. In my case it was the column with weight values – circled in black
  2. The range over which to test the first condition. In this case it was the column with dates – first red circle
  3. The condition that the first range has to meet – first green circle.
    • – I average the rows where date is same as, or before the date of this row. i.e. ‘<= row_date_cell’
  4. The range over which to test the second condition. It was again the column with dates – second red circle
  5. The condition that the second range has to meet – second green circle.
    • – I average the rows where date is greator than the date that is X days before the date in this row, i.e. ‘> row_date_cell - X’
    • – X defines how many days of data to average. In the graph above, I use a 30-day moving average. By changing the value in cell AM1, I could easily view the same graph with other values – a 7 day moving average, for instance.

Update: Spreadsheet with working sample.

Previously: Conditional sum product in Google Sheets

 

Exit mobile version