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​

 

[DRSteele]

Dr Demento, I would also recommend videos. I have benefitted immensely from watching the videos on theses two channels on youtube: https://www.youtube.com/user/ExcelIsFun/videos https://www.youtube.com/user/bjele123/videos

For me at least, nothing seems to bring complex formulas to light like visual instruction with narration. Both of those channels have very good titles and tags and are therefore searchable. Mike Girvin's ExcelIsFun channel also has a link to massive amounts of data contained in the source excel files for his spreadsheets.

 

[Dr. Demento]

DRSteele,

Much appreciated. I will definitely look into those.

I guess we all can aspire to be like Aladin Akyurek!!

 

[miles00577]

I am doing something similar, but adding more columns to indicate whether or not it was a conference game or not. The formula listed in here work perfect to find an overall streak or an overall home/away streak. In addition, I want to be able to find current streaks for conference home and away, not just overall home and away.

On my sheet, I have the following:
In Column J, I have where the game was played (Home = "H"/Away = "A"/Neutral = "N")
In Column K, I have if the game was a conference game (Y/N)
In Column L, it lists if the game was a win/loss/tie (W/L/T)

How would I get the formula to work to find, for example, the current streak for non-conference home games (search column J for "H"; column K for "N")??

 

[DRSteele]

If you'll be so kind as to find a way to wait for several days, I can provide all the conditional streak formulas you need.

Just a note: I'm fairly certain Posters in the forum are not supposed to ask the same question across different posts, like this one: https://www.mrexcel.com/forum/excel-questions/998977-trying-compile-different-w-l-streaks.html

 

[miles00577]

No problem. I'm in no hurry. I just figured the groundwork was set in this thread, and it was referenced when I started that thread. The other thread can be deleted to avoid confusion.

 

[DRSteele]

I haven't forgotten about you Miles. I'm having trouble with the project, so stay tuned until I suss it out.