Counting max consecutive positive and negative number in a row.

[ben951]

Hello all,

I need to count the largest number of consecutive positive numbers in k3 and negative numbers in k7.

I also need the sheet to update in real time.

I did not find the solution online.

Thank you.

[Bo_Ry]

Please try at
K3
=MAX(FREQUENCY(IF(K10:K19>0,ROW(K10:K19)),IF(K10:K19<=0,ROW(K10:K19))))
Press Ctrl+Shift+Enter

K7
=MAX(FREQUENCY(IF(K10:K19<0,ROW(K10:K19)),IF(K10:K19>=0,ROW(K10:K19))))
Press Ctrl+Shift+Enter

[modytrane]

I added a helper column with this formula:

Then it a matter of picking the Max and Min values.

See attached and hope it works for you.

[ben951]

Thx a lot, it works but I ran in another problem, when I use "","" to remove the zeros in the sheet it's counted as positive number, maybe there's an easy fix that still allow me to use "","", since I use it in other sheet also ?

[Bo_Ry]

Please try at L3
=MAX(FREQUENCY(IF((K10:K9999>0)*(K10:K9999<>""),ROW(K10:K9999)),IF(K10:K9999<=0,ROW(K10:K9999))))

[FlameRetired]

Another approach that eliminates / ignores the "" is to define a dynamic named range (DNR) in Name Manager.

In the attached please find a DNR that I named NumRng.
It is defined as

NumRng

=Sheet1!$K$10:INDEX(Sheet1!$K:$K,MATCH(1E+306,Sheet1!$K:$K,1))

Then array enter these modifications of Bo_Ry's:
For the Max positive

Please Login or Register  to view this content.

the Max negative

Please Login or Register  to view this content.

[ben951]

It work great.
Thank you all for your help.