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

#### Carni

##### New Member
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
 0 0 0 0 0 0.973543 0 0 0 0.817328 0 0 0 0.976807 0 0 0 0 0 0 0.764169 0 0 0 0.398993 0 0

<tbody>
</tbody>

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?

Last edited:

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Hi,

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

Code:
``=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

Hi,

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

Code:
``=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!

Replies
7
Views
179
Replies
10
Views
708
Replies
3
Views
353
Replies
15
Views
487
Replies
2
Views
381

1,203,617
Messages
6,056,310
Members
444,858
Latest member
ucbphd

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back