Calculating value for maximum consecutive value

Hi

I use MS 2007. I try to create formula to find the average of maximum consecutive value. According to my sample, I would like to get the average of maximum consecutive value from range a:p ;

PHP Code: 

To give more idea, the example is
         a  b  c  d  e  f  g h  i   k  l  m  n  o  p               q             r
firm                                                                maxyr       avr
xxxx   1   2  0  0  5  2  3  9 0  0  0  3   4 5   0                 4         (5+2+3+9)/4
xxxy   0   0  0   4  3 0  0  0 4  5  0  0   0 0   0                 2         ((4+3)/2) +((4+5)/2)
xxxz   0   5  2   4  5 3  0  0  0 6  7  0   0  0  0                 5         (5+2+4+5+3)/5 


How to get the formula for column r? Any help will be appreciated.

Note: I also attach my original excel file. In this original file, I would like to get the formular for column AM (AvrMx)

than_k22

Using your original sample file (you don't really need S:AH) but leaving them in for the time being for sake of continuity the following single cell calcs should do what you want:

AL2:
=MAX(FREQUENCY(IF(B2:Q2<>0,COLUMN(B2:Q2)),IF(B2:Q2=0,COLUMN(B2:Q2))))
confirmed with CTRL + SHIFT + ENTER
copied down

For the average of the Max run(s)

AM2:
=IF($AL2=0,"",MAX(SUBTOTAL(9,OFFSET($A2,0,$AL2-1+(ROW(A$1:INDEX(A:A,COLUMN($Q2)-$AL2))),1,-$AL2))*(SUBTOTAL(5,OFFSET($A2,0,$AL2-1+(ROW(A$1:INDEX(A:A,COLUMN($Q2)-$AL2))),1,-$AL2))>0))/$AL2)
confirmed with CTRL + SHIFT + ENTER
copied down

I have little doubt there is a more efficient method for the Average however.

edit: in retrospect I'm not sure the Average calc does exactly what you want re: multiple "maximum" runs ... ie 2 runs of 2
the array above will average the values of the run that has the highest aggregate value as opposed to the average of all of the averages...
let us know.

Per my edit... you could store the count of streaks to simplify the AVG calc, eg:

AN2:
=IF($AL2=0,"",SUM(--(FREQUENCY(IF($B2:$Q2<>0,COLUMN($B2:$Q2)),IF($B2:$Q2=0,COLUMN($B2:$Q2)))=$AL2)))
confirmed with CTRL + SHIFT + ENTER
copied down

Then modify the AVG calc accordingly

AM2:

=IF($AL2=0,"",SUMPRODUCT(SUBTOTAL(9,OFFSET($A2,0,$AL2-1+(ROW($A$1:INDEX($A:$A,COLUMN($Q2)-$AL2))),1,-$AL2))*(SUBTOTAL(5,OFFSET($A2,0,$AL2-1+(ROW($A$1:INDEX($A:$A,COLUMN($Q2)-$AL2))),1,-$AL2))>0))/($AL2*$AN2))
confirmed with Enter (not an Array per se)
copied down