I would like to take the average using an array of every 7 days on a Sunday and place the output on the same row but corresponding column of that Sunday.

If a cell in B is left blank then it should not be considered for an average. Meaning if on all numbers were filled for 6 days and the 7th was blank then the average should be the sum of the 6 days divided by 6 days.

Without an arrayformula() i was using:

=if(weekday(A8, 1)=1, average(B2:B8),"")

Any help would greatly be appreciated!

For example A8 is a Sunday so C8 should show the average of the range B2:B8

enter image description here


2 Answers 2


@Dal, given your sample data above, place the following formula into cell C1 of an otherwise empty Column C:

=ArrayFormula({"Some Header"; IF(WEEKDAY(A2:A)<>1,, (SUMIF(ROW(A2:A), "<="&ROW(A2:A), B2:B) - SUMIF(ROW(A2:A) ,"<="&ROW(A2:A)-7, B2:B)) / COUNTIFS(B2:B, "<>", ROW(A2:A), "<="&ROW(A2:A), ROW(A2:A), ">="&ROW(A2:A)-6))})


An array-literal is formed between the curly brackets { }.

The first element is the header, which you can change to whatever you like.

The semicolon means "continue in the cell below."

The IF first checks to see if the WEEKDAY of whatever is in each row of A2:A is a Sunday (i.e., weekday 1). If not, then that row will be left blank in Column C. If it is a Sunday, the formula continues.

The first SUMIF will SUM the values in B2:B for all rows earlier than the current row. From this is subtracted the SUM of any values in B2:B that are earlier than (i.e., <=) that row's date minus 7 days (i.e., anything before "this week"). The net SUM, then, is all values for "this week." This is then divided by the COUNTIF of values whose rows fall before the current row and after the current-row-minus-6 (again, the count of values for "this week" at every Sunday stop).

  • Awesome! This was exactly what I needed and I really appreciate the explanation so I can understand all of it!
    – Dal
    Oct 26, 2020 at 1:17
  • Happy to help. Since this seems to meet your need, would you take a moment and mark my reply as "Best Answer"? This helps other site visitors quickly find answers that work, as well as indicating to other contributors that this issue has been fully resolved.
    – Erik Tyler
    Oct 26, 2020 at 7:00
  • Sorry about that, i didnt know how to do it. It seems like it was accepted now
    – Dal
    Oct 27, 2020 at 2:34
  • No worries. It's not really of personal value to me. As I say, it just keeps things easier for others. So thank you!
    – Erik Tyler
    Oct 27, 2020 at 6:24

Using an unique arrayformula seems impossible in your case. I used indirect and sequence instead.

First column :


Second column :


then finally :


Example :

enter image description here

If a cell in B is left blank then it should not be considered for an average

You an use =AVERAGEIF(A1:A10,">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.