# Count longest consecutive run of zeros

1. ## Count longest consecutive run of zeros

I have a spreadsheet with daily rainfall data listed in column A. I want to find the longest period of no rainfall - I used to have a spreadsheet somewhere that used an array formula in a column next to the rainfall data and it counted the number of consecutive zero days, so I know it's possible! Can't for the life me remember the formula I used though.

Example: in column A (daily rainfall data) there is:

0.5, 0.0, 0.0, 1.0, 0.0, 0.0, 0.0, 1.5 etc

So in column B this would read as:

0, 1, 2, 0, 1, 2, 3, 0

etc, i.e. basically counting the number of zeros in column A, adding them up until a non-zero is found and starting again.

-Rob  Register To Reply

2. ## Re: Count longest consecutive run of zeros

Hi,

I'm sure they'll be a much neater (and non-array) solution, but, assuming your list starts in A2, enter this array formula in B2 and copy down as required:

=IF(A2<>0,0,MATCH(TRUE,N(OFFSET(A2,-ROW(INDIRECT("1:"&ROWS(\$1:1))),,,))<>0,0))

Regards  Register To Reply