# Streaks; Current Streak with Conditions

#### DRSteele

##### Well-known Member
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.

ABCD
1gamevisitorhost
21ab
32bc
43cb
54ba
65ab
76ac
87cb
98cb
109ac

<tbody>
</tbody>
Sheet36

Last edited:

### Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
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.

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

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

ABCDEFGHI
1gamevisitorhostStreak
21aba94V-3V-3
32bcb48H-3H-3
43cbc89H-1H-1
54ba
65ab
76ac
87cb
98cb
109ac
11

<tbody>
</tbody>
Sheet11

Worksheet Formulas
CellFormula
F2=LOOKUP(2,1/(\$B\$2:\$B\$100=E2),\$A\$2:\$A\$100)
G2=LOOKUP(2,1/(\$C\$2:\$C\$100=E2),\$A\$2:\$A\$100)
H2=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)))
I2=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))))

<tbody>
</tbody>

<tbody>
</tbody>

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.

Last edited:
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.

ABCDEFGHIJ
1GameVisitorHostVResultHResult
21AnaheimBostonWL
32BostonCalgaryLW
43CalgaryBostonOTW
54BostonAnaheimWOT
65AnaheimBostonWL
76AnaheimCalgaryWL
87CalgaryBostonOTW
98CalgaryBostonWOT
109AnaheimCalgaryWL
11
12TeamCurrent Streak
13AnaheimW-3
14BostonOT-1
15CalgaryL-1

</tbody>
Sheet36.B

Last edited:
Here's what I've come up with:

ABCDEFGHIJ
1gamevisitorhostVresultHresult
21AnaheimBostonWL
32BostonCalgaryLW
43CalgaryBostonOTW
54BostonAnaheimWOT
65AnaheimBostonWL
76AnaheimCalgaryWL
87CalgaryBostonOTW
98CalgaryBostonWOT
109AnaheimCalgaryWL
11
12TeamVisitor StreakHome StreakCombined Streak
13AnaheimWW-4OTOT-1WW-3
14BostonWW-1OTOT-1OTOT-1
15CalgaryWW-1LL-2LL-1

</tbody>
Sheet11

Worksheet Formulas
CellFormula
C13=LOOKUP(2,1/(\$B\$2:\$B\$10=\$B13),I\$2:I\$10)
E13=LOOKUP(2,1/(\$C\$2:\$C\$10=\$B13),J\$2:J\$10)
G13=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))

</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula
D13{=\$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)}
F13{=\$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)}
H13{=\$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)}

</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

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)}

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)}

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!

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!

Last edited:

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.

Replies
6
Views
207
Replies
6
Views
104
Replies
6
Views
109
Replies
3
Views
95
Replies
5
Views
363

1,203,515
Messages
6,055,851
Members
444,828
Latest member
StaffordStag

### 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.

### Which adblocker are you using?

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

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