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…

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

The formula used was:

Broken down into bits, the formula is:

‘`averageifs`

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

- The range with values to average. In my case it was the column with weight values – circled in black
- The range over which to test the first condition. In this case it was the column with dates – first red circle
- 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`

’

- – I average the rows where date is same as, or before the date of this row. i.e. ‘
- The range over which to test the second condition. It was again the column with dates – second red circle
- 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.

- – I average the rows where date is greator than the date that is

Previously: Conditional sum product in Google Sheets