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.
Thanks in advance
-Rob
Bookmarks