Counting consecutive negative value

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

"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.

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 =--(A10). And
put the following formula into B2 and copy down: =(A20)*(B1+1).

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

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)*(X0)
*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)))*(X0)
*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.

Try this array formula** :

=MAX(FREQUENCY(IF(A2:A250,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.

--
Biff
Microsoft Excel MVP


"Wallace" wrote in message
...
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

Explanation on how that formula works:

http://www.mrexcel.com/forum/showpos...78&postcount=9

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Try this array formula** :

=MAX(FREQUENCY(IF(A2:A250,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.

--
Biff
Microsoft Excel MVP


"Wallace" wrote in message
...
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

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

=LARGE(FREQUENCY((A2:A250)*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.



"T. Valko" wrote in message
...
Try this array formula** :

=MAX(FREQUENCY(IF(A2:A250,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.

--
Biff
Microsoft Excel MVP


"Wallace" wrote in message
...
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

"Steve Dunn" wrote:
Slight variation, which doesn't need to be array entered:
=LARGE(FREQUENCY((A2:A250)*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:A250,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 -----

"Steve Dunn" wrote in message
...
Slight variation, which doesn't need to be array entered:

=LARGE(FREQUENCY((A2:A250)*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.



"T. Valko" wrote in message
...
Try this array formula** :

=MAX(FREQUENCY(IF(A2:A250,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.

--
Biff
Microsoft Excel MVP


"Wallace" wrote in message
...
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