Counting consecutive negative value
Wallace wrote...
I have a column with positive and negative numbers.
I need to count the largest number of consecutive negative numbers and don't
really know how to do it.
If you mean consecutive cells containing negative numbers, you could
do this in a single cell array formula. If the data were in a single
column, multiple row range named X, try the array formula
=MAX(LARGE((ROW(X)-MIN(ROW(X))+1)*(X<0)
*SIGN((MMULT(--(ROW(X)+1=TRANSPOSE(ROW(X))),--X)=0)
+(ROW(X)=MAX(ROW(X)))),ROW(X)-MIN(ROW(X))+1)
-LARGE((ROW(X)-MIN(ROW(X)))*(X<0)
*SIGN((MMULT(--(ROW(X)-1=TRANSPOSE(ROW(X))),--X)=0)
+(ROW(X)=MIN(ROW(X)))),ROW(X)-MIN(ROW(X))+1))
Simpler using ancillary formulas/cells.
|