Determining dynamic array length on a constant number of observations

[lostest]

I am trying to calculate a trailing x-observation average on a column of data which includes both blank cells and non-blank cells.

For example, I want to take the average of the last 3 observations of my data, but the number of blank cells keep changing so I need to redefine the array length each time.

correct trailing average my loop
2 n/a n/a
3 n/a n/a
4 n/a n/a
<blank> 3 3 (2+3+4)/3
<blank> 3 2.33 (3+4+0)/3
7 3 1.33 (4+0+0)/3
<blank> 4.66 2.33 (7+0+0)/3
1 4.66 2.33 (0+7+0)/3
<blank> 4 2.66 (1+7+0)/3
2 4 2.66 (0+1+7)/3
5 2.5 1 (2+0+1)/3
6 2.66 2.33 (5+2+0)/3

Let's say x is the number of observation I want to capture in my calculation.

I have written this loop, but the problem is that it assumes my range length will always be equal to the number that the loop calculates the first time.
I try to reset w = 0 outside the loop but I keep receiving an error message.

 s= -1
        Do Until w = x
        s = s + 1
            w = Application.WorksheetFunction.CountIf(Range(Sheets("Sheet 1").Cells(a - 1, b), Sheets("Sheet 1").Cells(a - x - s, b)), ">0")
            average = Application.WorksheetFunction.Average(Range(Sheets("Sheet 1").Cells(a - 1, b), Sheets("Sheet 1").Cells(a - x - s, b)))
        Loop

Workbook example