Count consecutive wins

[livermore]

Hello.

I want to count the number of consecutive wins in a table.
Below I have an example.

[TABLE="width: 100"]
<tbody>[TR]
[TD]$40[/TD]
[/TR]
[TR]
[TD]-$20[/TD]
[/TR]
[TR]
[TD]$25[/TD]
[/TR]
[TR]
[TD]$10[/TD]
[/TR]
[TR]
[TD]$5[/TD]
[/TR]
[TR]
[TD]$41[/TD]
[/TR]
[TR]
[TD]$32[/TD]
[/TR]
[TR]
[TD]-$5[/TD]
[/TR]
[TR]
[TD]$9[/TD]
[/TR]
[TR]
[TD]-$12[/TD]
[/TR]
</tbody>[/TABLE]


My goal is to be able to count how many consecutive wins I was able to get.
The example shows 4 consecutive wins

[TABLE="width: 100"]
<tbody>[TR]
[TD]$25[/TD]
[/TR]
[TR]
[TD]$10[/TD]
[/TR]
[TR]
[TD]$5[/TD]
[/TR]
[TR]
[TD]$41[/TD]
[/TR]
[TR]
[TD]$32[/TD]
[/TR]
</tbody>[/TABLE]


I believe I need to use the FREQUENCY function but I'm completely lost regarding the bin.

Can you help me?
What the formula to achieve this?
Thank you.

 

[Eric W]

Try:

=MAX(FREQUENCY(IF(A1:A10>0,ROW(A1:A10)),IF(A1:A10<0,ROW(A1:A10))))

with Control+Shift+Enter.

 

[livermore]

This is it!
Many thanks.

Allow me to do another question.
I have a similar situation as below:

[TABLE="width: 100"]
<tbody>[TR]
[TD]-$50[/TD]
[/TR]
[TR]
[TD]$45[/TD]
[/TR]
[TR]
[TD]$6[/TD]
[/TR]
[TR]
[TD]$32[/TD]
[/TR]
[TR]
[TD]$4[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]$56[/TD]
[/TR]
[TR]
[TD]$21[/TD]
[/TR]
[TR]
[TD]-$45[/TD]
[/TR]
[TR]
[TD]-$12[/TD]
[/TR]
</tbody>[/TABLE]

Using your formula I would get 6 consecutive wins but there's a blank space between $4 and $56.
The formula is not taking the blank space in consideration, which would mean 4 consecutive wins and not 6.

Tried this formula:
=MAX(IF(ISNUMBER(A1:A10),FREQUENCY(IF(A1:A10>0,ROW(A1:A10)),IF(A1:A10<0,ROW(A1:A10)))))

But I'm getting a #N/A result

Regards.

 

[Nishant94]

This is it!
Many thanks.

Allow me to do another question.
I have a similar situation as below:

[TABLE="width: 100"]
<tbody>[TR]
[TD]-$50[/TD]
[/TR]
[TR]
[TD]$45[/TD]
[/TR]
[TR]
[TD]$6[/TD]
[/TR]
[TR]
[TD]$32[/TD]
[/TR]
[TR]
[TD]$4[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]$56[/TD]
[/TR]
[TR]
[TD]$21[/TD]
[/TR]
[TR]
[TD]-$45[/TD]
[/TR]
[TR]
[TD]-$12[/TD]
[/TR]
</tbody>[/TABLE]

Using your formula I would get 6 consecutive wins but there's a blank space between $4 and $56.
The formula is not taking the blank space in consideration, which would mean 4 consecutive wins and not 6.

Tried this formula:
=MAX(IF(ISNUMBER(A1:A10),FREQUENCY(IF(A1:A10>0,ROW(A1:A10)),IF(A1:A10<0,ROW(A1:A10)))))

But I'm getting a #N/A result

Regards.

Just a modification to Eric's Formula. Try this:

=MAX(FREQUENCY(IF(A1:A10>0,ROW(A1:A10)),IF(A1:A10<=0,ROW(A1:A10))))

 

[livermore]

Brilliant!

Easier than I thought.

Many, many thanks!