Google Sheets array formula containing average function to calculate moving average

Question

I have a number of columns which calculate a series of moving averages with formulas such as =average(E2:E4) (for a 3 day moving average). I'd like to use array formulas instead because I'm constantly adding data to the list. The problem is that the array I'm using for the average E1:E3 is in the same direction as the array formula I'd like to create, where the values would be, for example, E2:E and E4:E.

Example with a 3-day moving average

edit: I'm sorry. I was trying to be too succinct with my question. I would like a more generic formula, because I'm doing not just a 3-day moving average, but a 7 day, 14 day, and a 30 day each in separate columns. I don't really want to have to sum 30 items in a denominator.

Thank you for the responses so far.

Answer 1

Delete everything from F2:F, leaving only your header.

Then, if you are in an international locale that uses commas as parameter delineators, place the following formula in F2:

=ArrayFormula(IF(E2:E="",,(SUMIF(ROW(E2:E),"<="&ROW(E2:E)+1,E2:E)-SUMIF(ROW(E2:E),"<"&ROW(E2:E)-1,E2:E))/ARRAY_CONSTRAIN(ISNUMBER(E:E)+ISNUMBER(E2:E)+ISNUMBER(E3:E),ROWS(E2:E),1)))

If you are in an international locale that uses semicolons as parameter delineators, use this version in F2 instead:

=ArrayFormula(IF(E2:E="";;(SUMIF(ROW(E2:E);"<="&ROW(E2:E)+1;E2:E)-SUMIF(ROW(E2:E);"<"&ROW(E2:E)-1;E2:E))/ARRAY_CONSTRAIN(ISNUMBER(E:E)+ISNUMBER(E2:E)+ISNUMBER(E3:E);ROWS(E2:E);1)))

Essentially, this formula says, "Sum the numbers between one row back and one row forward and then divide by however many of those cells held real numbers (e.g., not text, Boolean or null values)."

Answer 2

Try:

=transpose(index(query(transpose(split(regexextract(substitute(substitute("~"&join("~",indirect("E1:E"&max(row(E:E)*(E:E<>""))))&"~~","~","#",sequence(counta(E:E),1,4,1)),"~","#",sequence(counta(E:E),1,1,1)),"#(.*)#"),"~")),"select avg(Col"&join("), avg(Col",sequence(counta(E:E)))&")"),2))

EDIT: Here's a more versatile formula that also works with blank cells in between:

=index(iferror(transpose(index(query(--transpose(split(regexextract(substitute(substitute("~"&join("~",indirect("E1:E"&max(row(E:E)*(E:E<>""))+1))&"~~","~","#",sequence(rows(indirect("E1:E"&max(row(E:E)*(E:E<>""))+1)),1,4,1)),"~","#",sequence(rows(indirect("E1:E"&max(row(E:E)*(E:E<>""))+1)),1,1,1)),"#(.*)#"),"~")),"select sum(Col"&join("), sum(Col",sequence(rows(indirect("E1:E"&max(row(E:E)*(E:E<>""))+1))))&")"),2)/transpose(mmult(transpose(n(isnumber(ifna(transpose(split(regexextract(substitute(substitute("~"&join("~",indirect("E1:E"&max(row(E:E)*(E:E<>""))+1))&"~~","~","#",sequence(rows(indirect("E1:E"&max(row(E:E)*(E:E<>""))+1)),1,4,1)),"~","#",sequence(rows(indirect("E1:E"&max(row(E:E)*(E:E<>""))+1)),1,1,1)),"#(.*)#"),"~")))))),{1;1;1})))))

Demo