"Steve Dunn" wrote:
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 -----

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



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

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