Help with Current and highest streaks

Telman86

New Member
Joined
Dec 6, 2019
Messages
17
Office Version
  1. 2013
Platform
  1. 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:

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
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")))
 
Upvote 0
Ah -- the current streak doesn't account for draws ...
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
Sorry shg, I just posted my reply above before I realised you had posted again. Let me have a look at it.

Thanks!
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
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))))
 
Upvote 0
Hi Bo_Ry, thanks for your suggestion, unfortunately none of your formulas give the correct results.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,037
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top