MODE

S

shabbyshic

=MAX(FREQUENCY(IF(D2:D254=0,COUNTIF(OFFSET(D2,,,ROW(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.
 
T

T. Valko

This array formula can replace your current formula:

=MAX(FREQUENCY(IF(rng=0,ROW(rng)),IF(rng<>0,ROW(rng))))

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
 
Top