How to find moving average of last 4 datapoints, excluding zero

[Carni]

Hi all

I've googled this a bunch and found a few responses that come close, but none of them seem to work.

Basically, I have data that looks like this:

(Column BH)
Expected goals during that game
[TABLE="width: 64"]
<tbody>[TR]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]0.973543[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]0.817328[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]0.976807[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]0.764169[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]0.398993[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]

On the column immediately to the right, I want the the cell to give me the average of the last 4 datapoints, that aren't zero. So next to the first zero above, I want the value 0.882961771, which is the average of the first 4 datapoints that aren't zero (0.973543, 0.817328, 0.976807, 0.764169).

Then I want to copy the formula down so that the average is only providing me with the last 4 datapoints up to that point. So the cell immediately next to the 2nd non-zero datapoint (0.817328), would average the four latest non-zero datapoints until then, which is (0.976807, 0.764169, 0.398993, + 1more outside of the range above).

Any ideas?

 

[James006]

Hi,

With your data in BH2:BH28 ... in cell BI7 you can have the following array formula:

=IFERROR(AVERAGE(LOOKUP(LARGE(IF((BH7:OFFSET(BH7,LARGE((BH7:BH28>0)*(ROW(BH7:BH28)),SUM((BH7:BH28>0)*1)-3)-ROW(),0))>0,ROW((BH7:OFFSET(BH7,LARGE((BH7:BH28>0)*(ROW(BH7:BH28)),SUM((BH7:BH28>0)*1)-3)-ROW(),0)))),{1,2,3,4}), ROW((BH7:OFFSET(BH7,LARGE((BH7:BH28>0)*(ROW(BH7:BH28)),SUM((BH7:BH28>0)*1)-3)-ROW(),0))), (BH7:OFFSET(BH7,LARGE((BH7:BH28>0)*(ROW(BH7:BH28)),SUM((BH7:BH28>0)*1)-3)-ROW(),0)))),"")

Then you can copy formula to cell BI11

Hope this will help

 

[Carni]

Hi,

With your data in BH2:BH28 ... in cell BI7 you can have the following array formula:

=IFERROR(AVERAGE(LOOKUP(LARGE(IF((BH7:OFFSET(BH7,LARGE((BH7:BH28>0)*(ROW(BH7:BH28)),SUM((BH7:BH28>0)*1)-3)-ROW(),0))>0,ROW((BH7:OFFSET(BH7,LARGE((BH7:BH28>0)*(ROW(BH7:BH28)),SUM((BH7:BH28>0)*1)-3)-ROW(),0)))),{1,2,3,4}), ROW((BH7:OFFSET(BH7,LARGE((BH7:BH28>0)*(ROW(BH7:BH28)),SUM((BH7:BH28>0)*1)-3)-ROW(),0))), (BH7:OFFSET(BH7,LARGE((BH7:BH28>0)*(ROW(BH7:BH28)),SUM((BH7:BH28>0)*1)-3)-ROW(),0)))),"")

Then you can copy formula to cell BI11

Hope this will help

Works perfectly! Thank you!

 

[James006]

Works perfectly! Thank you!

You are welcome ...