How to count the maximum number of consecutives?

[JP]

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

 

[JLatham]

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

 

[T. Valko]

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.

 

[JP]

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.

 

[JP]

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

 

[JP]

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]

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

 

[JP]

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]

You're welcome. Thanks for the feedback!

 

[JLatham]

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.