Help with Current and highest streaks

Telman86

New Member
Joined
Dec 6, 2019
Messages
17
Office Version
2013
Platform
Windows
Hi there,

I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a solution to my problem and can't find exactly what I'm looking for, so apologies if this has already been asked in the near past.

I am looking to calculate the longest and current streaks of Wins and Losses for my pool team players. The problem is that players may not play every week, but just because they don't play, this shouldn't interfere with their streaks. Therefore if a player wins two weeks running, then misses a week, and then wins the next two, his winning streak is still 4 over those five weeks. The streaks should only be restarted after a change in W or L.

I've set out an example below. This shows three players records over a 15 week period (my actual spreadsheet is for 36 weeks), although currently we have only played up to Week 11. Obviously W and L indicate a Win or Loss that week, whereas X indicates they did not play that week. All of these W's, L's and X's are automatically inserted into these cells by a formula dependent on data from elsewhere in my workbook. I am looking for formulas to go into columns Q, R & S, and I have indicated what the results should be.

Note that the array should always be B:P, i.e. all weeks up to the end of the season irrespective of whether the games have been played or not. Results are updated weekly and therefore the streaks need to automatically change as the data is populated into the new weeks.

Please also note that I am looking for formulas only. I can't do VBA!!

I hope this is clear, and I appreciate any help you can give.

A​
B​
C​
D​
E​
F​
G​
H​
I​
J​
K​
L​
M​
N​
O​
P​
Q​
R​
S​
1​
WEEK 1
WEEK 2
WEEK 3
WEEK 4
WEEK 5
WEEK 6
WEEK 7
WEEK 8
WEEK 9
WEEK 10
WEEK 11
WEEK 12WEEK 13WEEK 14WEEK 15
LONGEST W STREAK
LONGEST L STREAK
CURRENT STREAK
2​
John
W​
W​
X​
W​
W​
L​
X​
L​
W​
W​
W​
4
2
W3
3​
Peter
X​
W​
L​
L​
W​
X​
X​
W​
X​
W​
W​
4
2
W4
4​
George
X​
L​
L​
X​
L​
W​
X​
L​
X​
L​
X​
1
3
L2
 
Last edited by a moderator:

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,714
Office Version
2010
Platform
Windows
A​
B​
C​
D​
E​
F​
G​
H​
I​
J​
K​
L​
M​
N​
O​
P​
Q​
R​
S​
1​
W1
W2
W3
W4
W5
W6
W7
W8
W9
W10
W11
W12
W13
W14
W15
Longest W
Longest L
Current
2​
Alan
L​
W​
L​
L​
W​
L​
L​
L​
L​
W​
W​
L​
2​
4​
L1​
3​
Barb
L​
L​
L​
L​
W​
L​
L​
L​
W​
L​
W​
L​
W​
1​
4​
W1​
4​
Cain
L​
W​
L​
W​
W​
W​
L​
W​
W​
L​
L​
L​
L​
3​
4​
L4​
5​
Dana
W​
L​
W​
W​
L​
L​
W​
W​
W​
L​
W​
L​
3​
2​
L1​
6​
Eric
L​
W​
W​
L​
W​
W​
L​
L​
W​
L​
L​
L​
W​
L​
2​
3​
L1​
7​
Fran
L​
W​
L​
L​
W​
W​
L​
L​
W​
W​
L​
2​
2​
L1​
8​
Gary
W​
L​
W​
L​
L​
W​
W​
L​
L​
L​
W​
W​
L​
W​
W​
2​
3​
W2​
9​
Hana
L​
W​
L​
W​
W​
W​
L​
L​
W​
3​
2​
W1​
10​
Ivan
W​
L​
L​
W​
L​
L​
W​
L​
L​
L​
W​
L​
1​
3​
L1​
11​
Jane
L​
W​
W​
W​
L​
W​
W​
W​
L​
W​
W​
W​
L​
L​
3​
2​
L2​
12​
Kent
L​
L​
L​
L​
L​
W​
L​
L​
L​
W​
L​
L​
L​
1​
5​
L3​
13​
Leah
L​
W​
L​
W​
W​
L​
W​
W​
L​
W​
L​
2​
1​
L1​
14​
Mark
W​
L​
W​
W​
W​
L​
W​
L​
L​
W​
W​
W​
L​
L​
3​
2​
L2​
15​
Nina
W​
W​
W​
W​
L​
L​
L​
L​
W​
W​
W​
L​
L​
W​
W​
4​
4​
W2​

In Q2, confirmed with Ctrl+Shift+Enter,
Code:
=MAX(FREQUENCY(IF(B2:P2 = "W", COLUMN(B2:P2)), IF(B2:P2 <> "W", COLUMN(B2:P2))))
In R2, confirmed with Ctrl+Shift+Enter,
Code:
=MAX(FREQUENCY(IF(B2:P2 = "L", COLUMN(B2:P2)), IF(B2:P2 <> "L", COLUMN(B2:P2))))
In S2
Code:
=INDEX(B2:P2, MATCH("z", B2:P2)) &
IF(INDEX(B2:P2, MATCH("z", B2:P2)) = "L", LOOKUP(99, COLUMN($B2:$P2) / ($B2:$P2 = "L")) - LOOKUP(99, COLUMN($B2:$P2) / ($B2:$P2 = "W")),
                                          LOOKUP(99, COLUMN($B2:$P2) / ($B2:$P2 = "W")) - LOOKUP(99, COLUMN($B2:$P2) / ($B2:$P2 = "L")))
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,714
Office Version
2010
Platform
Windows
Ah -- the current streak doesn't account for draws ...
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,714
Office Version
2010
Platform
Windows
A​
B​
C​
D​
E​
F​
G​
H​
I​
J​
K​
L​
M​
N​
O​
P​
Q​
R​
S​
1​
W1
W2
W3
W4
W5
W6
W7
W8
W9
W10
W11
W12
W13
W14
W15
Lonegst W
Longest L
Current
2​
Alan
W​
W​
W​
L​
X​
W​
L​
L​
L​
L​
W​
W​
3​
4​
W2​
3​
Barb
L​
X​
X​
L​
W​
L​
W​
X​
L​
L​
L​
W​
L​
1​
3​
L1​
4​
Cain
X​
L​
L​
W​
X​
W​
W​
L​
X​
L​
L​
W​
L​
2​
2​
L1​
5​
Dana
W​
L​
L​
L​
L​
W​
L​
X​
L​
W​
W​
W​
3​
4​
W3​
6​
Eric
W​
L​
W​
L​
X​
W​
X​
L​
W​
L​
L​
W​
W​
W​
3​
2​
W3​
7​
Fran
X​
L​
L​
W​
L​
W​
X​
L​
W​
L​
W​
1​
2​
W1​
8​
Gary
L​
L​
L​
L​
L​
X​
X​
X​
W​
W​
X​
L​
L​
L​
L​
2​
5​
L4​
9​
Hana
L​
L​
X​
X​
W​
X​
W​
X​
W​
1​
2​
W1​
10​
Ivan
W​
W​
L​
W​
L​
L​
W​
W​
L​
X​
W​
L​
2​
2​
L1​
11​
Jane
L​
W​
L​
W​
X​
W​
W​
X​
L​
L​
X​
W​
W​
X​
2​
2​
X1​
12​
Kent
W​
L​
L​
L​
W​
L​
W​
W​
L​
X​
X​
L​
X​
2​
3​
X1​
13​
Leah
X​
W​
W​
W​
X​
X​
X​
L​
W​
W​
X​
3​
1​
X1​
14​
Mark
W​
W​
W​
W​
W​
L​
L​
W​
W​
X​
L​
L​
X​
W​
5​
2​
W1​
15​
Nina
L​
L​
L​
L​
X​
L​
L​
W​
X​
L​
W​
L​
L​
X​
L​
1​
4​
L1​

In S2,

VBA Code:
=INDEX(B2:P2, MATCH("zzz", B2:P2)) & MATCH("zzz", A2:P2) -
 IF(INDEX(B2:P2, MATCH("z", B2:P2)) = "W", LOOKUP(99, COLUMN(B2:P2) / ((B2:P2 = "L") + (B2:P2 = "X"))),
 IF(INDEX(B2:P2, MATCH("z", B2:P2)) = "L", LOOKUP(99, COLUMN(B2:P2) / ((B2:P2 = "X") + (B2:P2 = "W"))),
 IF(INDEX(B2:P2, MATCH("z", B2:P2)) = "X", LOOKUP(99, COLUMN(B2:P2) / ((B2:P2 = "W") + (B2:P2 = "L"))))))
I expect that could be shortened.
 

Telman86

New Member
Joined
Dec 6, 2019
Messages
17
Office Version
2013
Platform
Windows
Thanks for your time shg, but unfortunately none of your formulas work as they are not taking account of the X values, which should be ignored within any streaks.

For example;
The longest winning streak for John is W W X W W (B2 to B6) - The result should be 4 as X should be ignored.
The longest winning streak for Peter is W X X W X W W (F3 to L3) - The result should be 4 as the X's should be ignored.
The current streak for George is L X L X (I4 to L4) - The result should be L2 as the X's should be ignored.

Any ideas?

Cheers
 

Telman86

New Member
Joined
Dec 6, 2019
Messages
17
Office Version
2013
Platform
Windows
Sorry shg, I just posted my reply above before I realised you had posted again. Let me have a look at it.

Thanks!
 

Telman86

New Member
Joined
Dec 6, 2019
Messages
17
Office Version
2013
Platform
Windows
Hi shg, it still looks like your formulas are not ignoring the X's.

In your example, Hana should have a current streak of W3 (not W1). F9 to J9 reads: W X W X W. Therefore this should be a streak of three Wins as the X's need to be ignored.
 

Telman86

New Member
Joined
Dec 6, 2019
Messages
17
Office Version
2013
Platform
Windows
Also shg, on your last row (Nina), the longest losing streak should be 6, i.e. B15 to H15 - L L L L X L L (ignoring the X!)

Cheers!
 

Bo_Ry

Board Regular
Joined
Oct 27, 2018
Messages
65
Office Version
365
Platform
Windows
Please try

Win Q2
=MAX(FREQUENCY(IF(B2:P2="W",COLUMN(B2:P2)),IF(B2:P2="L",COLUMN(B2:P2))))

Lost R2
=MAX(FREQUENCY(IF(B2:P2="W",COLUMN($B2:$P2)),IF(B2:P2="L",COLUMN(B2:P2))))

Current S2
=LOOKUP(2,1/(B2:P2<>"")/(B2:P2<>"X"),B2:P2)&LOOKUP(99,FREQUENCY(IF($B2:$P2=LOOKUP(2,1/(B2:P2<>"")/(B2:P2<>"X"),B2:P2),COLUMN($B2:$P2)),IF($B2:$P2=IF(LOOKUP(2,1/(B2:P2<>"")/(B2:P2<>"X"),B2:P2)="W","L","W"),COLUMN($B2:$P2))))
 

Telman86

New Member
Joined
Dec 6, 2019
Messages
17
Office Version
2013
Platform
Windows
Hi Bo_Ry, thanks for your suggestion, unfortunately none of your formulas give the correct results.
 

Forum statistics

Threads
1,081,958
Messages
5,362,397
Members
400,673
Latest member
RobBro1987

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top