Help with Current and highest streaks

[Telman86]

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 12

WEEK 13

WEEK 14

WEEK 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​

 

[Bo_Ry]

Really?
it works fine here

Book1
	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 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
5	Nina	W	W	W	W	L	L	L	L	W	W	W	L	L	W	W	4	4	W2
Sheet1

Cell Formulas
Range	Formula
Q2:Q5	Q2	=MAX(FREQUENCY(IF(B2:P2="W",COLUMN(B2:P2)),IF(B2:P2="L",COLUMN(B2:P2))))
R2:R5	R2	=MAX(FREQUENCY(IF(B2:P2="L",COLUMN($B2:$P2)),IF(B2:P2="W",COLUMN(B2:P2))))
S2:S5	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))))

 

[Fluff]

@Bo_Ry whilst those work for me, do they CSE entry on older versions of Xl?

 

[Scott T]

@Bo_Ry whilst those work for me, do they CSE entry on older versions of Xl?

Yes, they need CSE If you do not have the new calculation engine.

 

[shg]

I know you said no VBA, but it's just so easy ...

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

M​

N​

O​

P​

Q​

R​

S​

T​

1​

​

W1​

W2​

W3​

W4​

W5​

W6​

W7​

W8​

W9​

W10​

W11​

W12​

W13​

W14​

W15​

Lonegst W​

Longest L​

Current​

2​

Alan

W​

L​

L​

L​

X​

L​

L​

X​

L​

W​

L​

L​

​

​

​

1​

6​

L2​

Q2:S2: {=streaks(B2:P2)}

3​

Barb

L​

X​

W​

W​

W​

W​

L​

L​

W​

L​

W​

W​

L​

​

​

4​

2​

L1​

4​

Cain

L​

L​

L​

L​

X​

L​

L​

X​

W​

L​

L​

L​

X​

​

​

1​

6​

L3​

5​

Dana

L​

W​

W​

X​

X​

L​

L​

W​

W​

X​

L​

W​

​

​

​

2​

2​

W1​

6​

Eric

L​

X​

L​

W​

L​

W​

X​

L​

W​

X​

L​

X​

L​

L​

​

1​

3​

L3​

7​

Fran

L​

L​

L​

X​

W​

W​

L​

W​

W​

W​

L​

​

​

​

​

3​

3​

L1​

8​

Gary

W​

X​

L​

L​

X​

L​

W​

L​

W​

X​

W​

W​

W​

L​

W​

4​

3​

W1​

9​

Hana

W​

W​

W​

X​

X​

X​

X​

W​

W​

​

​

​

​

​

​

5​

0​

W5​

10​

Ivan

W​

W​

X​

X​

W​

L​

W​

X​

X​

X​

W​

X​

​

​

​

3​

1​

W2​

11​

Jane

L​

W​

L​

W​

L​

L​

L​

W​

W​

L​

L​

X​

W​

W​

​

2​

3​

W2​

12​

Kent

L​

L​

X​

L​

W​

W​

W​

L​

W​

W​

L​

L​

L​

​

​

3​

3​

L3​

13​

Leah

W​

L​

L​

W​

L​

W​

W​

W​

W​

X​

X​

​

​

​

​

4​

2​

W4​

14​

Mark

X​

L​

L​

L​

W​

W​

L​

L​

W​

L​

W​

L​

X​

X​

​

2​

3​

L1​

15​

Nina

L​

W​

L​

L​

W​

W​

W​

L​

L​

W​

L​

W​

L​

X​

L​

3​

2​

L2​

Function Streaks(r As Range)
  ' Returns 3-element array for longest winning streak,
  ' longest losing streak, and current streak

  Dim sPatt         As String
  Dim LW            As Long
  Dim LL            As Long
  Dim CS            As String
  sPatt = Replace(UCase(Cat(r, "")), "X", "")

  For LW = 0 To Len(sPatt)
    If Not sPatt Like "*" & String(LW + 1, "W") & "*" Then Exit For
  Next LW

  For LL = 0 To Len(sPatt)
    If Not sPatt Like "*" & String(LL + 1, "L") & "*" Then Exit For
  Next LL

  CS = Right(sPatt, 1)
  CS = CS & (Len(sPatt) - Len(RTrim(Replace(sPatt, CS, " "))))

  Streaks = Array(LW, LL, CS)
End Function

Function Cat(vInp As Variant, _
             Optional sSep As String = ",", _
             Optional bCatEmpty As Boolean = False) As String
  ' Catenates the elements of vInp separated by sSep
  ' Empty values and null strings are ignored unless bCatEmpty is True

  Dim vItem         As Variant
  Dim sItem         As String

  If bCatEmpty Then
    For Each vItem In vInp
      Cat = Cat & CStr(vItem) & sSep
    Next vItem

  Else
    For Each vItem In vInp
      sItem = CStr(vItem)
      If Len(sItem) Then Cat = Cat & sItem & sSep
    Next vItem
  End If

  If Len(Cat) Then Cat = Left(Cat, Len(Cat) - Len(sSep))
End Function

 

[Telman86]

Hi Bo_Ry, I've copied and pasted your formulas exactly.

I've also added Nina to my test sheet as you have done above.

These are the results I am getting:

	LONGEST W STREAK	LONGEST L STREAK	CURRENT STREAK
John	7	0	W7
Peter	4	1	W4
George	1	1	L1
Nina	7	2	W2

I'm using Excel 2013, and I also don't know what CSE means!

 

[Scott T]

When you commit the formula use CONTROL+SHIFT+ENTER not just enter.

What you type in
=MAX(FREQUENCY(IF(B2:P2="W",COLUMN(B2:P2)),IF(B2:P2="L",COLUMN(B2:P2))))

What you will see if entered with CSE. the {} tells you that Excel knows to calculate it as an array formula
{=MAX(FREQUENCY(IF(B2:P2="W",COLUMN(B2:P2)),IF(B2:P2="L",COLUMN(B2:P2))))}

 

[Telman86]

Thanks shg, but I've never used VBA and I have no idea where to begin to even put that information. I know I can copy and paste your code, but I don't know how or where to paste it!

 

[Telman86]

Hi Scott T,

Yes, I have been doing that (didn't know it was called CSE). All the formulae have ended up inside the squiggly brackets.

 

[Scott T]

It works for me. you cannot type in the {} you must use CSE

Book1
	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 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
5	Nina	W	W	W	W	L	L	L	L	W	W	W	L	L	W	W	4	4	W2
Sheet3

Cell Formulas
Range	Formula
Q2:Q5	Q2	{=MAX(FREQUENCY(IF(B2:P2="W",COLUMN(B2:P2)),IF(B2:P2="L",COLUMN(B2:P2))))}
R2:R5	R2	{=MAX(FREQUENCY(IF(B2:P2="L",COLUMN($B2:$P2)),IF(B2:P2="W",COLUMN(B2:P2))))}
S2:S5	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))))}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[Telman86]

Yep, that's how I did it.

By the way, my brain is obviously a bit slow tonight - I thought CSE was some technical function until I saw the CTRL+SHIFT+ENTER!

Anyway, that's what I've done and the results are as per above. I don't understand why I am getting incorrect results.