Average of Rolling Averages - Undefined data points

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.

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

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

       -A-- ---B--- --C-- --------------------------------------------D--------------------------------------------
   1   Nums Weights  Avg                                                                                           
   2      4       1 16.67 C2: =SUMPRODUCT(rgnInp, rgnWgt) / (ROWS(rgnInp) - conFiltLen + 1)                            
   3      5       2                                                                                                
   4      1       3       conFiltLen RefersTo: =4                                                                  
   5      6       4                                                                                                
   6      2       4       frmZero RefersTo: =MATCH(0, $A:$A, 0) - 1                                                
   7      7       4                                                                                                
   8      3       3       rgnInp RefersTo: =INDEX($A:$A, ROW(Sheet1!$A$2)):INDEX(Sheet1!$A:$A, frmZero)            
   9      1       2                                                                                                
  10     12       1       B3 and down: =MAX(0, MIN(ROWS(A$1:A3) - 1, ROWS(rgnInp) - ROWS(A$1:A3) + 2, conFiltLen) )
  11      0       0                                                                                                
  12    100       0                                                                                                
  13    100       0                                                                                                
  14    100       0                                                                                                
  15    100       0                                                                                                
  16    100       0                                                                                                
  17    100       0

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.

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

other things work just fine

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

Post a workbook with some actual data.