I have these columns:
- A person_id
- B date
- C cupcakes_eaten
- D date-35 (35 days prior)
There can be multiple rows with the same person_id and same date.
There can be days with zero rows for a particular person_id (so, missing periods).
I can use this this formula to calculate the 35-day moving average of cupcakes eaten:
=SUMIFS(C:C,A:A,A2,B:B,">="&D2, B:B, "<="&B2)/35
This works, and I can replicate this to all other rows.
Is there a more elegant way to do this using ArrayFormula so I don't have to replicate it to all the other rows?