0

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

1
0

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

4
  • Awesome! This was exactly what I needed and I really appreciate the explanation so I can understand all of it! – Dal Oct 26 '20 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 '20 at 7:00
  • Sorry about that, i didnt know how to do it. It seems like it was accepted now – Dal Oct 27 '20 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 '20 at 6:24
0

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 :

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, privacy policy and cookie policy

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