0

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.

1
  • 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

1 Answer 1

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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