Thread: counting consecutive cells that are greater than or less than 0

My goal is to look at a column of numbers and count the number of consecutive cells that are positive (or negative), while ignoring blank cells that may be in between them.

Using the first formula on this thread
http://www.excelforum.com/excel-gene...-than-0-a.html

I was able to adapt it to my own data, but whenever I had blank cells in my data the results were not reliable.

the formula i am using now is
=IF(COUNT(1/((A1:A27<0)*(A2:A28<0))),MAX(FREQUENCY(IF((A1:A27<0)*(A2:A28<0),ROW(A2:A28)),IF((A1:A27<0)*(A2:A28>= 0),ROW(A1:A27))))+1,0)
for a bunch of data in column A.

With limited knowledge of excel I'm not exactly sure how this formula works, so I'm not sure how to tell it to ignore all blank cells.

I attached an example that shows how it fails with the blank cells

Hopefully someone can help me out!
Thanks a lot

Based on your sample:

Code:

=MAX(FREQUENCY(IF(A1:A26<0,ROW(A1:A26)),IF(ISNUMBER(A1:A26)*(A1:A26>=0),ROW(A1:A26))))
confirmed with CTRL + SHIFT + ENTER