Calculating value for maximum consecutive value

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.