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

Question

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

Answer 1

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

Answer 2

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