Average of Rolling Averages - Undefined data points

[apierce]

Wondering if someone can help me figure this out.

I have a row with numbers, like this:

4 5 1 6 2 7 3 1 12 0 -

I need to get the rolling average of the sum of 4 numbers, but stopping at the point in the row where the first 0 occurs. So in this example, the result would be:

the average of the following sums: (4+5+1+6) & (5+1+6+2) & (1+6+2+7) & (6+2+7+3) & (2+7+3+1) & (7+3+1+12)

Where the first 0 comes will be variable and thus I need a function that determines that.

Any ideas? Thanks.

[zbor]

I've did it with 2 extra lines... but someone will bring them to one so I'll post it here just in case

Book1.xls

Row 4 are data
Row 3 is sum of first 4 numbers
Row 2 find where is 0 and takes 4 (because of 4 numbers you summing).
In this case 6-4=2... result will be sum of 2 groups

And with sunmproduct or sumif you sumerise them (in yellow).

Now... Someone put it in one line

[shg]

I transposed your row to a column (which could be put back), and used a helper column (which could probably be eliminated).

Please Login or Register  to view this content.

[JBeaucaire]

Yeah, I don't even think I understood your two line approach.

Anyway, two formulas, one to make sure we can even start the counts (no zero in the first 4 cells) and then the second formula that copies across and generates usable values.

Then a normal Averaging at the end.

[zbor]

Oh, yes, didn't see it's average... But it's just SUM()/4, right?

other things work just fine

[JBeaucaire]

No, it's an average of the resulting values, not an average of the subsets of 4 cells.

[apierce]

Thanks for the responses....The latest solution yields a correct answer but my problem is that I have hundreds of rows with values that I need the average for, so creating hundreds more of columns would be undesirable. Any way to get the results all calculated in one cell next to the list of values?

[shg]

Post a workbook with some actual data.

[apierce]

Heres the sample and desired info.