Count consecutive cells if negative

  • Thread starter Thread starter deacs
  • Start date Start date
D

deacs

Hi all,

Is there a way to do this in excel?

I have a column of numbers and I would like to count what the max
number of consecutive negatives are. For instance, if I have a series
of numbers (1,3,5,-2,-5,3,-7)), I would like it to return 2 as the max
number of consecutive negatives.

Thanks in advance!
 
There may be a more elegant way, but here is one that worked for me:

Assuming the data resides in Column A (I will use A2:A10 for illustration),
I copy the following formula down next to the data in Column B from B2 to
B10:

=IF(A2<0,IF(B1>0,B1+1,1),0)

Then in Cell B11 (at the end of the data), I use the max formula to compute
the highest sequence of negatives:

=MAX(B2:B11)

Hope that made sense ;-/
 
This formula borrowed from Harlan Grove should work

=MAX(MMULT(TRANSPOSE(--($A$1:$A$7<0)),(TRANSPOSE(ROW(INDIRECT("1:"&COUNT(--(
$A$1:$A$7<0)))))>=ROW(INDIRECT("1:"&COUNT(--($A$1:$A$7<0)))))-SIGN(MMULT((RO
W(INDIRECT("1:"&COUNT(--($A$1:$A$7<0))))<=(TRANSPOSE(--($A$1:$A$7<0)=0))*TRA
NSPOSE(ROW(INDIRECT("1:"&COUNT(--($A$1:$A$7<0))))))+0,(TRANSPOSE(ROW(INDIREC
T("1:"&COUNT(--($A$1:$A$7<0)))))>=ROW(INDIRECT("1:"&COUNT(--($A$1:$A$7<0))))
)+0))))

entered with ctrl + shift & enter

where A1:A7 is the range with the values
 
Back
Top