How do I get the current number of consecutive wins

[isaacmeck]

Hi,

See sheet attached.

I'm looking for a formula for B3 so I can get the current win streak. I want this to update when I add data to week 10 onwards without me having to change the formula. I only want the current win streak and not previous ones.

Thanks

[AliGW]

Welcome to the forum.

An oft posed question! Have a look here: https://www.excelforum.com/excel-for...in-streak.html

[XLent]

...when I add data to week 10 onwards without me having to change the formula. I only want the current win streak and not previous ones.

one option

B3: =MAX(0,MATCH(2,INDEX(1/(C3:R3="Win"),0))-MATCH(2,INDEX(1/(C3:R3="Lose"),0)))
copied down

[isaacmeck]

So I understand what they've done on that thread but I don't want to use numbers to represent wins and losses if possible. Is there a formula that can count the number of times "win" appears consecutively and when "lose" comes up, the count resets to zero again.

[isaacmeck]

one option

B3: =MAX(0,MATCH(2,INDEX(1/(C3:R3="Win"),0))-MATCH(2,INDEX(1/(C3:R3="Lose"),0)))
copied down

That works great. How do I change the formula if I want incorporate draw and forfeit?

[XLent]

if they break the sequence then

B3: =MAX(0,MATCH(2,INDEX(1/(C3:R3="Win"),0))-MATCH(2,INDEX(1/(C3:R3<>"Win")/(C3:R3<>""),0)))

if they persist the sequence

B3: =MAX(0,MATCH(2,INDEX(1/(C3:R3<>"Lose")/(C3:R3<>""),0))-MATCH(2,INDEX(1/(C3:R3="Lose"),0)))

[isaacmeck]

Thanks.

If I want to also add "win by forfeit" and I want this to add to the consecutive wins then how would I do that?

[XLent]

assuming Forfeit and Draw break the winning sequence, i.e. option 1 in prior post, then:

B3: =MAX(0,MATCH(2,INDEX(1/(LEFT(C3:R3,3)="Win"),0))-MATCH(2,INDEX(1/(LEFT(C3:R3,3)<>"Win")/(C3:R3<>""),0)))

[isaacmeck]

I mean if someone now can "win by forfeit" or "win", I want their number of consecutive wins to increase and the sequence to break when anything else happens. What is the formula please??

[XLent]

that provided (post#8); please test.

[isaacmeck]

Thats brilliant. Thanks for your help

[isaacmeck]

When someone has lost all their games, the winning streaks shows N/A. How can this be fixed?

[Bo_Ry]

Please try at B3

=COUNTA(C3:R3)-IFERROR(MATCH(2,INDEX(1/(LEFT(C3:R3,3)<>"win")/(C3:R3>""),)),0)

[AliGW]

Try this:

=IFERROR(your_chosen_formula,"Lost All Games")

[isaacmeck]

Please try at B3

=COUNTA(C3:R3)-IFERROR(MATCH(2,INDEX(1/(LEFT(C3:R3,3)<>"win")/(C3:R3>""),)),0)

This formula works but seems to return a value of 2 more than it should every time

[isaacmeck]

Try this:

=IFERROR(your_chosen_formula,"Lost All Games")

This works but when someone starts playing in week 4 and there isn't any data in weeks 1 to 3, it still counts weeks 1 to 3 as a win.

[AliGW]

This works perfectly on the sample data you provided:

=IFERROR(MAX(0,MATCH(2,INDEX(1/(LEFT(C3:R3,3)="Win"),0))-MATCH(2,INDEX(1/(LEFT(C3:R3,3)<>"Win")/(C3:R3<>""),0))),"All Losses")

Perhaps you need to provide a more realistic dataset instead of constantly adding criteria and therefore shifting the goalposts - don't expect your helpers to be able to anticipate all these twists and turns!

[isaacmeck]

Sorry about that, could you please incorporate the point I previously mentioned and then that should be it

[AliGW]

No. I won't help any further until you provide a more realistic set of sample data - sorry.

Administrative Note:

Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

For normal conversational replies, try using the QUICK REPLY box below or the REPLY button instead of REPLY WITH QUOTE.

[isaacmeck]

Please try at B3

=COUNTA(C3:R3)-IFERROR(MATCH(2,INDEX(1/(LEFT(C3:R3,3)<>"win")/(C3:R3>""),)),0)

Actually that works perfectly, Thank you very much for your help!

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Next time, think through all possible outcomes before providing sample data. Thank you.