is it possible to write an array formula in google sheets that will increment cell referents?

Question

i am tracking traffic data and keeping a running tally of new members, as well as a running average.

what i am currently doing is manually adding the formula to each cell every day, for e. g.

avg. daily attrition	avg. total attrition
=AVERAGE(E2:E3)	=AVERAGE(H2:H3)
=AVERAGE(E2:E4)	=AVERAGE(H2:H4)
=AVERAGE(E2:E5)	=AVERAGE(H2:H5)
=AVERAGE(E2:E6)	=AVERAGE(H2:H6)
(...)	(...)
=AVERAGE(E2:E366)	=AVERAGE(H2:H366)

what i would like is a formula that only adds the new average to the latest cell, and doesn't affect the previous averages of older cells. so, using =AVERAGE(E2:E366) for the whole column is obviously right out.

i'm sure there's a way to do this, i just don't know what it is. i tried looking up array formulae, but based on how =ARRAY works, i don't think that would be suitable.

thanks in advance.

ETA: i found a tutorial. thank you for the answer.

Answer 1

Try

=transpose(index(query(transpose(split(split(substitute(join("~",E2:E366),"~","#",sequence(rows(E2:E366))),"#"),"~")),"select avg(Col"&join("), avg(Col",sequence(rows(E2:E366)))&")"),2))

With transpose(split(split(substitute(join("~",E2:E366),"~","#",sequence(rows(E2:E366))),"#"),"~")) we are creating a table like the one in C3:G8:

and with "select avg(Col"&join("), avg(Col",sequence(rows(E2:E366)))&")" (C9) we return the average of the values in the columns.