# Moving average up until every listed week

I'm trying to automatically calculate a new average every week, so I can see the evolution of the average over the past weeks.

I have one column with the week numbers. The second column contains an amount of applicants per week. In the third column, I wish to get the average of the number of applicants this week and all the weeks prior. So if I have three rows, the first average will only use the the first row's number of applicants, the second row's average will be row 1 + row 2 divided by 2, the third row's average will be the amount of applicants of row 1 + 2 + 3 divided by 3.

There's probably a simple way to achieve this, but I cannot figure it out, and I don't know how to phrase it to find the answer in Google.

I can simply drag the formula down and that works, but I'm using ArrayFormulas everywhere and I'd like this average to be generated automatically as well.

• Consider sharing a publicly editable sample spreadsheet with realistic-looking data, and showing your hand-entered expected results there. May 6, 2021 at 12:53

One way to do this is to calculate a cumulative sum of column `B` and divide it by the number of rows where numbers are present. Try this:

``````=arrayformula(
iferror( 1 / (
isnumber(B2:B)
*
countif( if(isnumber(B2:B), row(B2:B)), "<=" & row(B2:B) )
/
dsum(
if( row(B:B) <= transpose(row(B2:B)), B:B ),
sequence(rows(A2:B)),
transpose(iferror(column(A2:B) / 0))
)
) )
)
``````