Average of the last n populated rows

[schneider1972]

Hello,

I have some data that has numbers in rows. I want to calculate the average of the last 4 rows only if populated (i.e. > 0). So if a previous row is 0 I want to go back 1 additional row.

There is no pattern for when the score will be 0, I also would like to do the same theory but for the last 2 populated rows and the last 8 populated rows.

Here is a pic of what the data is like:

 

[hagia_sofia]

Hello, maybe something like:

=MAP(B2:B12,LAMBDA(x,AVERAGE(TAKE(FILTER(B2:x,B2:B2:x<>0),-4))))

 

[schneider1972]

perfect thanks Hagia_Sofia

 

[hagia_sofia]

Many thanks for the feedback! Just a minor correction: the B2:B2:x part should be B2:x - a misclick on my side.