## TIL: Moving average with missing periods in Google Sheets

`=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.

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…

## TIL: Conditional sum product in Google Sheets

`=SUMPRODUCT(D5:D1009="",C5:C1009,B5:B1009)`

Just learnt an easy way to do conditional `SumProduct`1 calculations in Google Sheets2.

In above example, I wanted the SumProduct of values in columns `B` and `C`, when the respective cell in column `D` was empty.

The solution is to simply introduce the conditional column as another input, with the condition stated in the input.

Sheets does the SumProduct calculations, with each `true` as `1`, and `false` as `0`, thus making it a Conditional SumProduct :)