How to count the maximum number of consecutives?

  • Thread starter Thread starter JP
  • Start date Start date
J

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
 
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)?
 
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.
 
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.
 
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).
 
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
 
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
 
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.
 
Back
Top