How to count the maximum number of consecutives?
 

I need to determine the maximum number of consecutive negative numbers in a
column. The column contains formulas that evaluate to a positive number, a
negative number, zero or "" (blank). I cannot add an additional column. The
following are the results of formulas in cells A1:A11 that need a formula in
A12 that evaluates to 3, the maximum number of consecutive negative numbers
counting cells A2, A6 and A7.

1
-2
""
""
""
-6
-7
8
-9
""
-11

How to count the maximum number of consecutives?
 

You're sure you can't us an extra column somewhere?? Even a hidden one?
There are many examples of how to do this published on the internet (search
for a phrase like "find sequence of negative numbers in excel" and you'll see
them). But almost all involve a 'helper' column. My own solution required a
helper column also, and I was looking for one without it.
Could you accept a User Defined Function (a VB macro that you can use on a
worksheet just like a built in function)?


"JP" wrote:

I need to determine the maximum number of consecutive negative numbers in a
column. The column contains formulas that evaluate to a positive number, a
negative number, zero or "" (blank). I cannot add an additional column. The
following are the results of formulas in cells A1:A11 that need a formula in
A12 that evaluates to 3, the maximum number of consecutive negative numbers
counting cells A2, A6 and A7.

1
-2
""
""
""
-6
-7
8
-9
""
-11

How to count the maximum number of consecutives?
 

Try this array formula** :

=MAX(FREQUENCY(IF(A1:A11<0,ROW(A1:A11)),IF(A1:A11 =0,IF(A1:A11<"",ROW(A1:A11)))))

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


"JP" wrote in message
...
I need to determine the maximum number of consecutive negative numbers in a
column. The column contains formulas that evaluate to a positive number,
a
negative number, zero or "" (blank). I cannot add an additional column.
The
following are the results of formulas in cells A1:A11 that need a formula
in
A12 that evaluates to 3, the maximum number of consecutive negative
numbers
counting cells A2, A6 and A7.

1
-2
""
""
""
-6
-7
8
-9
""
-11

How to count the maximum number of consecutives?
 

Thanks very much for your reply but I can't use a hidden column and I need to
avoid user-defined functions and macros. If it is not possible using a
formula or array formula, then at least I'll know that and can stop
trying--so I appreciate your help.

"JLatham" wrote:

You're sure you can't us an extra column somewhere?? Even a hidden one?
There are many examples of how to do this published on the internet (search
for a phrase like "find sequence of negative numbers in excel" and you'll see
them). But almost all involve a 'helper' column. My own solution required a
helper column also, and I was looking for one without it.
Could you accept a User Defined Function (a VB macro that you can use on a
worksheet just like a built in function)?


"JP" wrote:

I need to determine the maximum number of consecutive negative numbers in a
column. The column contains formulas that evaluate to a positive number, a
negative number, zero or "" (blank). I cannot add an additional column. The
following are the results of formulas in cells A1:A11 that need a formula in
A12 that evaluates to 3, the maximum number of consecutive negative numbers
counting cells A2, A6 and A7.

1
-2
""
""
""
-6
-7
8
-9
""
-11

How to count the maximum number of consecutives?
 

Thank you for your reply. This is the type of solution I've been looking for
but it evaluates incorrectly to 2 rather than 3 (using my example).

"T. Valko" wrote:

Try this array formula** :

=MAX(FREQUENCY(IF(A1:A11<0,ROW(A1:A11)),IF(A1:A11 =0,IF(A1:A11<"",ROW(A1:A11)))))

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


"JP" wrote in message
...
I need to determine the maximum number of consecutive negative numbers in a
column. The column contains formulas that evaluate to a positive number,
a
negative number, zero or "" (blank). I cannot add an additional column.
The
following are the results of formulas in cells A1:A11 that need a formula
in
A12 that evaluates to 3, the maximum number of consecutive negative
numbers
counting cells A2, A6 and A7.

1
-2
""
""
""
-6
-7
8
-9
""
-11



.

How to count the maximum number of consecutives?
 

My error, it evaluates perfectly. Thank you so much, T. Valko! I've been
working on this for a long time and it's finally solved thanks to you.

Best regards,
JP

"JP" wrote:

Thank you for your reply. This is the type of solution I've been looking for
but it evaluates incorrectly to 2 rather than 3 (using my example).

"T. Valko" wrote:

Try this array formula** :

=MAX(FREQUENCY(IF(A1:A11<0,ROW(A1:A11)),IF(A1:A11 =0,IF(A1:A11<"",ROW(A1:A11)))))

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


"JP" wrote in message
...
I need to determine the maximum number of consecutive negative numbers in a
column. The column contains formulas that evaluate to a positive number,
a
negative number, zero or "" (blank). I cannot add an additional column.
The
following are the results of formulas in cells A1:A11 that need a formula
in
A12 that evaluates to 3, the maximum number of consecutive negative
numbers
counting cells A2, A6 and A7.

1
-2
""
""
""
-6
-7
8
-9
""
-11



.

How to count the maximum number of consecutives?
 

This works perfectly, many many thanks and happy New Year to you.

"T. Valko" wrote:

Try this array formula** :

=MAX(FREQUENCY(IF(A1:A11<0,ROW(A1:A11)),IF(A1:A11 =0,IF(A1:A11<"",ROW(A1:A11)))))

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


"JP" wrote in message
...
I need to determine the maximum number of consecutive negative numbers in a
column. The column contains formulas that evaluate to a positive number,
a
negative number, zero or "" (blank). I cannot add an additional column.
The
following are the results of formulas in cells A1:A11 that need a formula
in
A12 that evaluates to 3, the maximum number of consecutive negative
numbers
counting cells A2, A6 and A7.

1
-2
""
""
""
-6
-7
8
-9
""
-11



.

How to count the maximum number of consecutives?
 

I finally understand how this is working. It's a brilliant and elegant
solution that is greatly appreciated. Thanks once again, Biff and happy
trails.

JP


"T. Valko" wrote:

Try this array formula** :

=MAX(FREQUENCY(IF(A1:A11<0,ROW(A1:A11)),IF(A1:A11 =0,IF(A1:A11<"",ROW(A1:A11)))))

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


"JP" wrote in message
...
I need to determine the maximum number of consecutive negative numbers in a
column. The column contains formulas that evaluate to a positive number,
a
negative number, zero or "" (blank). I cannot add an additional column.
The
following are the results of formulas in cells A1:A11 that need a formula
in
A12 that evaluates to 3, the maximum number of consecutive negative
numbers
counting cells A2, A6 and A7.

1
-2
""
""
""
-6
-7
8
-9
""
-11



.

How to count the maximum number of consecutives?
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"JP" wrote in message
...
I finally understand how this is working. It's a brilliant and elegant
solution that is greatly appreciated. Thanks once again, Biff and happy
trails.

JP


"T. Valko" wrote:

Try this array formula** :

=MAX(FREQUENCY(IF(A1:A11<0,ROW(A1:A11)),IF(A1:A11 =0,IF(A1:A11<"",ROW(A1:A11)))))

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


"JP" wrote in message
...
I need to determine the maximum number of consecutive negative numbers
in a
column. The column contains formulas that evaluate to a positive
number,
a
negative number, zero or "" (blank). I cannot add an additional
column.
The
following are the results of formulas in cells A1:A11 that need a
formula
in
A12 that evaluates to 3, the maximum number of consecutive negative
numbers
counting cells A2, A6 and A7.

1
-2
""
""
""
-6
-7
8
-9
""
-11



.

How to count the maximum number of consecutives?
 

As you now know, there's almost always a solution (key word 'almost'), and in
this case I just kind of figured if Biff couldn't do it, it couldn't be done.
Glad he was able to solve your problem.

"JP" wrote:

Thanks very much for your reply but I can't use a hidden column and I need to
avoid user-defined functions and macros. If it is not possible using a
formula or array formula, then at least I'll know that and can stop
trying--so I appreciate your help.

"JLatham" wrote:

You're sure you can't us an extra column somewhere?? Even a hidden one?
There are many examples of how to do this published on the internet (search
for a phrase like "find sequence of negative numbers in excel" and you'll see
them). But almost all involve a 'helper' column. My own solution required a
helper column also, and I was looking for one without it.
Could you accept a User Defined Function (a VB macro that you can use on a
worksheet just like a built in function)?


"JP" wrote:

I need to determine the maximum number of consecutive negative numbers in a
column. The column contains formulas that evaluate to a positive number, a
negative number, zero or "" (blank). I cannot add an additional column. The
following are the results of formulas in cells A1:A11 that need a formula in
A12 that evaluates to 3, the maximum number of consecutive negative numbers
counting cells A2, A6 and A7.

1
-2
""
""
""
-6
-7
8
-9
""
-11