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?

  • 1
    Your question title refers to "missing periods" but there is no mention of "missing periods" in your question. Would you please clarify this (or edit the title). – Tedinoz Apr 26 at 6:53
  • Why not use a pivot table and filter out the blanks? Seems like the perfect use case for one. – enderland May 4 at 11:50
  • I actually use a pivot table against the sheet which this sheet described in my question. here's the pivot table: =QUERY('group-by-day'!A:E,"select B, sum(E) where B > date '2020-09-01' group by B pivot A", 1). What do you recommend for my sheet described in the question? – John Bachir May 4 at 12:50

First, delete any unneeded blank rows at the bottom of the sheet. Then try this in row 2 of a free column:

  iferror( 1 / ( 35 /
        (A:A = transpose(A2:A)) 
        (row(A:A) <= transpose(row(A2:A))) 
        (B:B >= transpose(D2:D)), 
  ) )

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.