This array formula can replace your current formula:

=MAX(FREQUENCY(IF(rng=0,ROW(rng)),IF(rng<0,ROW(rn g))))

For the average (array):

=AVERAGE(IF(FREQUENCY(IF(rng=0,ROW(rng)),IF(rng<0 ,ROW(rng))),FREQUENCY(IF(rng=0,ROW(rng)),IF(rng<0 ,ROW(rng)))))

Neither formula accounts for empty cells.

Biff

"shabbyshic" <u34829@uwe wrote in message news:734575011ddcf@uwe...
=MAX(FREQUENCY(IF(D2:D254=0,COUNTIF(OFFSET(D2,,,RO W(INDIRECT("1:"&ROWS(D2:
D254)))),"<"&0)),ROW(INDIRECT("1:"&ROWS(D2:D254)) )-1))

After calculating the max frequency of "0" occuring in a string of data, I
needed the ability to return the average occurrence of the "0"s.