Streaks; Current Streak with Conditions

[DRSteele]

I would like to create an array formula, without helper columns if possible, to identify a Current Streak. I tried and tried but I'm just missing a key piece to the puzzle. So for team A the current streak is three games as visitor, or V-3; for team B it's H-3; and for team C it's H-1.

	A	B	C	D
a	b
b	c
c	b
b	a
a	b
a	c
c	b
c	b
a	c

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFF2CC"]game[/TD]
[TD="bgcolor: #FFF2CC"]visitor[/TD]
[TD="bgcolor: #FFF2CC"]host[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]2[/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]3[/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]4[/TD]

[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]5[/TD]

[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]6[/TD]

[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]7[/TD]

[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]8[/TD]

[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]9[/TD]

[TD="align: right"][/TD]

</tbody>

Sheet36​

 

[lrobbo314]

I don't understand how you're getting these streaks. For team A the most consecutive times it's listed looks like 2 as a visitor. Same with B. For Host games, I only see 2 Bs in a row.

 

[DRSteele]

Team A's games were Games 1,4,5,6, and 9; team A's recent streak was 3 as visitor, thusly VHVVV
For B, it's Games 1, 2,3,4,5,7, and 8; the recent streak is 3 as host. HVHVHHH
C, Games 2,3,6,7,8, & 9; the recent streak is 1 as host. HVHVVH

 

[Eric W]

Here's one way (well, maybe 2):

	A	B	C	D	E	F	G	H	I
game	visitor	host	Streak
a	b	a	V-3	V-3
b	c	b	H-3	H-3
c	b	c	H-1	H-1
b	a
a	b
a	c
c	b
c	b
a	c

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"][/TD]

[TD="align: right"]9[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]2[/TD]

[TD="align: right"][/TD]

[TD="align: right"]4[/TD]
[TD="align: right"]8[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]3[/TD]

[TD="align: right"][/TD]

[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]9[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet11​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]F2[/TH]
[TD="align: left"]=LOOKUP(2,1/($B$2:$B$100=E2),$A$2:$A$100)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]G2[/TH]
[TD="align: left"]=LOOKUP(2,1/($C$2:$C$100=E2),$A$2:$A$100)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]H2[/TH]
[TD="align: left"]=IF(F2>G2,"V-"&COUNTIFS($B$2:$B$100,E2,$A$2:$A$100,">"&MIN(F2,G2)),"H-"&COUNTIFS($C$2:$C$100,E2,$A$2:$A$100,">"&MIN(F2,G2)))[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]I2[/TH]
[TD="align: left"]=IF(LOOKUP(2,1/($B$2:$B$100=E2),$A$2:$A$100)>LOOKUP(2,1/($C$2:$C$100=E2),$A$2:$A$100),"V-"&COUNTIFS($B$2:$B$100,E2,$A$2:$A$100,">"&MIN(LOOKUP(2,1/($B$2:$B$100=E2),$A$2:$A$100),LOOKUP(2,1/($C$2:$C$100=E2),$A$2:$A$100))),"H-"&COUNTIFS($C$2:$C$100,E2,$A$2:$A$100,">"&MIN(LOOKUP(2,1/($B$2:$B$100=E2),$A$2:$A$100),LOOKUP(2,1/($C$2:$C$100=E2),$A$2:$A$100))))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



F and G are helper columns, then the formula in H2 gives you the result you wanted.

And since you didn't want helper columns, I incorporated them into the formula in I2. Much longer, and I'd recommend using the helper columns and hiding them, but that's up to you. These are technically array formulas, but they don't need Control+Shift+Enter. I'm going to ponder on this a bit more to see if I can improve on this a bit.

 

[DRSteele]

Eric, thanks tons for your efforts! That works great and it's exactly what I asked for.

It works great on the scant sample data I provided; but unfortunately I can't seem to scale this up to the larger purpose. Perhaps you have some insight. This is what I need.

	A	B	C	D	E	F	G	H	I	J
Anaheim	Boston	W	L
Boston	Calgary	L	W
Calgary	Boston	OT	W
Boston	Anaheim	W	OT
Anaheim	Boston	W	L
Anaheim	Calgary	W	L
Calgary	Boston	OT	W
Calgary	Boston	W	OT
Anaheim	Calgary	W	L
Anaheim
Boston
Calgary

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFF2CC"]Game[/TD]
[TD="bgcolor: #FFF2CC, align: right"][/TD]
[TD="bgcolor: #FFF2CC"]Visitor[/TD]
[TD="bgcolor: #FFF2CC"]Host[/TD]
[TD="bgcolor: #FFF2CC, align: right"][/TD]
[TD="bgcolor: #FFF2CC, align: right"][/TD]
[TD="bgcolor: #FFF2CC, align: right"][/TD]
[TD="bgcolor: #FFF2CC, align: right"][/TD]
[TD="bgcolor: #FFF2CC"]VResult[/TD]
[TD="bgcolor: #FFF2CC"]HResult[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FCE4D6"]Team[/TD]
[TD="bgcolor: #FCE4D6"]Current Streak[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="bgcolor: #F4B084"]W-3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="bgcolor: #F4B084"]OT-1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="bgcolor: #F4B084"]L-1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet36.B​

 

[Eric W]

Here's what I've come up with:

	A	B	C	D	E	F	G	H	I	J
game	visitor	host	Vresult	Hresult
Anaheim	Boston	W	L
Boston	Calgary	L	W
Calgary	Boston	OT	W
Boston	Anaheim	W	OT
Anaheim	Boston	W	L
Anaheim	Calgary	W	L
Calgary	Boston	OT	W
Calgary	Boston	W	OT
Anaheim	Calgary	W	L
Team	Visitor Streak	Home Streak	Combined Streak
Anaheim	W	W-4	OT	OT-1	W	W-3
Boston	W	W-1	OT	OT-1	OT	OT-1
Calgary	W	W-1	L	L-2	L	L-1

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]9[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet11​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C13[/TH]
[TD="align: left"]=LOOKUP(2,1/($B$2:$B$10=$B13),I$2:I$10)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E13[/TH]
[TD="align: left"]=LOOKUP(2,1/($C$2:$C$10=$B13),J$2:J$10)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]G13[/TH]
[TD="align: left"]=IF(LOOKUP(2,1/($B$2:$B$10=$B13),$A$2:$A$10)>LOOKUP(2,1/($C$2:$C$10=$B13),$A$2:$A$10),LOOKUP(2,1/($B$2:$B$10=$B13),$I$2:$I$10),LOOKUP(2,1/($C$2:$C$10=$B13),$J$2:$J$10))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D13[/TH]
[TD="align: left"]{=$C13&"-"&COUNTIF(OFFSET($B$1,MAX(IF(($B$2:$B$10=$B13)*($I$2:$I$10<>$C13),ROW($I$2:$I$10))),0):$B$10,$B13)}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F13[/TH]
[TD="align: left"]{=$E13&"-"&COUNTIF(OFFSET($C$1,MAX(IF(($C$2:$C$10=$B13)*($J$2:$J$10<>$E13),ROW($J$2:$J$10))),0):$C$10,$B13)}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]H13[/TH]
[TD="align: left"]{=$G13&"-"&COUNTIF(OFFSET($B$1,MAX(IF((($B$2:$B$10=$B13)*($I$2:$I$10<>$G13))+(($C$2:$C$10=$B13)*($J$2:$J$10<>$G13)),ROW($J$2:$J$10))),0):$C$10,$B13)}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



Each streak requires a helper cell. You can combine them together easy enough, but if you do that for the Combined Streak, you'd end up with a monster formula.

Let me know if this is what you're looking for.


Edit:

Here's a bit shorter formula for G13:

{=INDEX({"W","L","OT"},MOD(MAX(IF($B$2:$C$10=$B13,ROW($B$2:$C$10)+MATCH($I$2:$J$10,{"W","L","OT"},0)/10)),1)*10)}

 

[Eric W]

And using the same idea as the shorter G13 formula, here's a shorter H13 formula:

{=$G13&"-"&COUNTIF(OFFSET($B$1,MAX(IF(($B$2:$C$10=$B13)*($I$2:$J$10<>$G13),ROW($B$2:$C$10))),0):$C$10,$B13)}

 

[DRSteele]

Eric, thank you very much for all this work you've done.

I was able to adapt these terrific formulas for my needs. I am happy to report that I now have a Workbook that uses Queries to download the entire NHL schedule (which is 30 teams and 82 games per team for a total of 1260 games) and scores on a daily basis. Thanks to your formulas and others', the Workbook automatically creates the standings and dozens of other analyses, including the streaks (recent, winning, point, losing, pointless). Prior to your formulas, I had to use a grid of 30Rx82C that had each game's result, from which I was able to ascertain the recent streak but was bulky and slow. But you fixed it all!

 

[Dr. Demento]

Eric,

That's so impressive I'm blinded with [Excel] Science! Out of curiousity, where would you recommend beginning to learn how to use formulas to your prodigious level? I've mainly been focused on VBA but I definitely can see the utility of learning the formula side as well.

BTW, there used to be an ice cream shop in Woods Cross that used to sell ice cream that was so thick they called it 'concrete.' I think they're out of business now Miss UT in the fall!

 

[Eric W]

DRSteele, your spreadsheet sounds very impressive! I'm glad I could contribute a little.

Dr. Demento, (winding up my radio!), I'm largely self-taught. I just worked on Excel for work and figured out the basics of formulas. Like you, I focused on VBA for a long time (I'm a programmer by trade). It really wasn't until I started frequenting this site that my formula skills really took a step up. I thought I was fairly advanced in Excel when I started here, only to learn how much I didn't know! I frequently saw formulas that made me blink and say, "I didn't know you could do that!" (Still do!) But then I'd dive into it and figure out how it worked, then I could use pieces of it for future projects. I'd recommend something similar, find an interesting formula, especially if it relates to something you actually will use, and deconstruct it. Ask questions if there's something that doesn't make sense. There are other good sites where people explain formulas, so use Google as necessary. I don't have any personal book recommendations, but I have a list somewhere from someone else I can give you if you want.

And I know Nielsen's Custard very well! They make frozen custard they call concrete, I eat there too often! Still open.