Counting consecutive negative value

[Wallace]

Hi,

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

Wallace

 

[Joe User]

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.

First, do you really want to count "consecutive negative numbers" per se, or
do you really want to count "number of consecutive cells with negative
numbers"?

In the first case, consecutive cells with values -1, -2 and -4 would count
as 2; in the second case, they would count as 3.

You should be able to adapt the following approach in either case. But if
you need assistance with that, you need to be more precise.

This might not be the best approach, but it's simple. I believe someone (T
"Biff" Valko?) was able to implement this approach in a single array formula.

Suppose your numbers are in A1:A30. In B1, put the formula =--(A1<0). And
put the following formula into B2 and copy down: =(A2<0)*(B1+1).

Then =MAX(B1:B30) is the largest number of consecutive cells with negative
numbers.

 

[Harlan Grove]

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.

 

[RagDyeR]

This *array* formula will return max number of consecutive negatives,
*BUT* ... will *not* allow empty or zero cells to break the negative
sequence count:

=MAX(FREQUENCY(IF(A1:A20<0,ROW(A1:A20)),IF(A1:A20>0,ROW(A1:A20))))


This *array* formula will return max number of consecutive negatives,
*BUT* ... will only count *actual* consecutive negative cells:

=MAX(FREQUENCY(IF(A1:A20<0,ROW(A1:A20)),IF(A1:A20>=0,ROW(A1:A20))))

--
Array formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of the
regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, CSE *must* be used when
revising the formula.

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


Hi,

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

Wallace

 

[T. Valko]

Try this array formula** :

=MAX(FREQUENCY(IF(A2:A25<0,ROW(A2:A25)),IF(A2:A25>=0,ROW(A2:A25))))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[T. Valko]

Explanation on how that formula works:

http://www.mrexcel.com/forum/showpost.php?p=2278578&postcount=9

 

[Steve Dunn]

Slight variation, which doesn't need to be array entered:

=LARGE(FREQUENCY((A2:A25<0)*ROW(A2:A25),(A2:A25>=0)*ROW(A2:A25)),2)

MAX in this case would return the total number of negative values, so
LARGE(,2) is the figure we're looking for.

 

[Joe User]

Slight variation, which doesn't need to be array entered:
=LARGE(FREQUENCY((A2:A25<0)*ROW(A2:A25),(A2:A25>=0)*ROW(A2:A25)),2)

Does not work in a number of specific cases. For example, when A2:A25
contains only negative numbers. For another example, 12 consecutive
negative numbers, then zero, then 11 consecutive negative numbers.

Interestingly, using MAX instead of LARGE works in those cases. But MAX
does not work in random cases. I suspect for the same reason that LARGE
fails in the cases above.

Apparently, the problem is: as written above, the first FREQUENCY parameter
contains zeroes for cells where the condition is false. Those increase the
"bin" for the first row that meets the condition A2:A25>=0.

In contrast, with the array formula using IF(A2:A25<0,ROW(A2:A25)), the
first FREQUENCY parameter contains FALSE truth values instead of zero.
Apparently, FREQUENCY ignores truth values as well as blank and text cells.
(Not documented in the Excel 2003 offline Help page.)


----- original message -----