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
<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?
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:
