Moving average up until every listed week

Question

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.

Answer 1

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)) 
    ) 
  ) ) 
)