maximum length of dry spell, data in a single column

[sania]

Hi all,

here is a formula to calculate maximum consecutive wet days;

=MAX(FREQUENCY(IF(B2:K2>0,COLUMN(B2:K2)),IF(B2:K2<=0,COLUMN(B2:K2))))

this formula works fine when my data is in a row like (B2:k2) data is 1 0 0 0 1 1 0 1 0 0 answer is 2.
(0 is for dry days, 1 is for wet days)

now my data is in a column (B2:B11)

1
0
0
0
1
1
0
1
0
0

I have changed formula like;


=MAX(FREQUENCY(IF(B2:B11>0,COLUMN(B2:B11)),IF(B2:B11<=0,COLUMN(B2:B11))))


but this is not giving the right answer, pls. help what is wrong?

thank you
sania

[rylo]

Hi

Try
=MAX(FREQUENCY(IF(B2:B11>0,ROW(B2:B11)),IF(B2:B11<=0,ROW(B2:B11))))

rylo