# Can I implement this moving average with missing periods using ARRAYFORMULA?

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?

• 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). Apr 26 at 6:53
• Why not use a pivot table and filter out the blanks? Seems like the perfect use case for one. 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? 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:

``````=arrayformula(
iferror( 1 / ( 35 /
dsum(
if(
(A:A = transpose(A2:A))
*
(row(A:A) <= transpose(row(A2:A)))
*
(B:B >= transpose(D2:D)),
C1:C,
iferror(1/0)
),
sequence(rows(A2:A)),
if({1;1},)
)
) )
)
``````