Finding the longest sequence of 0's in a row
ok i have just done this but in 2 steps
it is not very clean but it works and is easy to understand
it relies on you having enough empty columns at the right (or you can adapt
it for another sheet)
step 1
leave a blank column after your dates
then start with this formula in the next column and drag it across and down
until this new area is the same size as your data area with the dates in it
=IF(A1<0,0,IF(K1=0,1,K1+1)) [assuming A1 is the first date and K1 is the
empty column you just made]
the basic idea is that as you spread the formula across and down it makes a
running total of how many 0s in a row. when it hits a value it goes back to
0.
|