# Google sheets array formula for taking average every Sunday and output is in same row corresponding column

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

• Please remember that as per site guidelines when an answer addresses your question, accept it and even upvote it so others can benefit as well. Oct 24, 2020 at 13:42

@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))})
``````

HOW IT WORKS:

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. 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! Oct 27, 2020 at 6:24

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

First column :

``````=ArrayFormula(IF(WEEKDAY(A2:A)=1,VLOOKUP(row(A2:A),F:G,2,false),""))
``````

Second column :

``````={ArrayFormula("B"&
SEQUENCE(10,1,3,7)&":B"&SEQUENCE(10,1,9,6))
,SEQUENCE(10,1,3,7)}
``````

then finally :

``````=AVERAGEIF(INDIRECT(E2),">0")
``````

Example :

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

You an use `=AVERAGEIF(A1:A10,">0")`