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.

Please Login or Register  to view this content.

Workbook example

[shg]

Welcome to the board.

First, please take a few minutes to read the forum rules, and then edit your post to add CODE tags.

Second, please post an example workbook with a clear indication of what you're trying to do.

Thanks.

[lostest]

The main problem of the loop above is that I need it to run until w = x, but then I need w to reset to not x and run the loop again for the next a value. When I try to reset w I get an error message and nothing works.

[shg]

Could you explain, in words and whole sentences, what the data in each column represents, and what calculation you are trying to perform?

[lostest]

The data is simply the status of a variable recorded every time period. Let's say the data is the height of a balloon recorded every minute, and I want to figure out the average height over the past four minutes FOR WHICH WE HAVE DATA. That is, sometimes the machine which does the recording is broken and spits out an erroneous value of 0. In those cases the zero value should be ignored and the range length should increase until we have four non-zero recordings.

In other words, the code first looks over how many periods we want to average. Let say the code looks at the past four observations, it realizes only two of the observations are greater than zero. Because two is not equal to four the code now adds an additional cell to the range, the new cell is a non-zero number. Now we have 3 non zero numbers in a 5 cell range, but because three does not equal four the loop repeats until we have four non-zero numbers in a range of cells which may change each time depending on the distribution of zero values in our time series.

Hope this brings some additional clarity.

[shg]

Like this?

Row\Col	A	B	C	D
1	DATE	number	last 3 > 0
2	1			C2: {=IF(OR(B2="", COUNTIF(B$1:B2, ">0") < 3), "", AVERAGEIF(INDEX(B:B, LARGE(IF((B$1:B2 > 0)*(ISNUMBER(B$1:B2)), ROW(B$1:B2)), 3)):B2, ">0"))}
3	2
4	3	235.75
5	4	211.25
6	5	0.00
7	6	207.50	218.17
8	7	174.00	197.58
9	8
10	9	208.00	196.50
11	10	179.00	187.00
12	11
13	12	175.43	187.48
14	13	203.92	186.11
15	14	241.75	207.03
16	15
17	16	205.75	217.14
18	17
19	18	178.75	208.75
20	19	248.75	211.08
21	20
22	21	213.25	213.58
23	22
24	23	194.50	218.83
25	24
26	25	196.75	201.50
27	26
28	27	162.50	184.58
29	28	241.00	200.08

[lostest]

Yes! Exactly like that.

One more thing, I need to make column C continuous. Blank cells in column C should have the value of the most prior non-blank cell. Say, cell C9 should have a value of cell C8. I can accomplish this by using the is IF(ISNUMBER()) functions, but again, I run into a problem if two blank cells are one after the other.

[shg]

Row\Col	A	B	C	D
1	Date	Num	Last 3 > 0
2	1			C2: {=IFERROR(AVERAGEIF(INDEX(B:B, LARGE(IF((B$1:B2 > 0)*(ISNUMBER(B$1:B2)), ROW(B$1:B2)), 3)):B2, ">0"), "")}
3	2
4	3	235.75
5	4	211.25
6	5	-
7	6	207.50	218.17
8	7	174.00	197.58
9	8		197.58
10	9	208.00	196.50
11	10	179.00	187.00
12	11		187.00
13	12	175.43	187.48
14	13	203.92	186.12
15	14	241.75	207.03
16	15		207.03
17	16	205.75	217.14
18	17		217.14
19	18	178.75	208.75
20	19	248.75	211.08
21	20		211.08
22	21	213.25	213.58
23	22		213.58
24	23	194.50	218.83
25	24		218.83
26	25	196.75	201.50
27	26		201.50
28	27	162.50	184.58
29	28	241.00	200.08

[lostest]

Thank you. Everything has been solved.

[shg]

You're welcome. You could finish the paperwork by marking the thread as Solved, as described in the forum rules.