Track Multiple Streaks By Date in Excel

[budmaniac]

I have a spreadhseet in which I track a sports teams progress throughout the year.  I would like to formulate a way to track automatically their current win/loss streak based on date.  For example, if the team wins consecutive games from April 1-5, I would like a line state 5.  Then if they lose from April 6-8, I would like the next line to show a 3.  Bear in mind, that the team will have days off.  If I am not getting too progressive, I would like the column to the left to show the beginning and ending dates of the streak.  There are no ties, so each line would alternate between a winning and losing streak.  I have no experience with Visual Basic.

[#2]

The attached file has column labels for Date, Team, Result, Unbroken, Cumulative, Streak, Start, and End. The formulas under Unbroken determine whether or not the Result for the Team in the last Game is the same as the Result of this game. Those under Cumulative return the streak, starting with 1. Those under Streak return the streak starting with 2. Those under Start return the start date of the streak. Finally, those under End return the end date of the streak. 

[#3]

Thanks for the example.  I will look to adjust and implement for my needs, but it appears to be more than enough to start.  Much appreciation.

Bud

[#4]

Ok, so I have reviewed the information from Twifoo.  I am not sure it is what I need.  I have attached some information showing parts of my spreadsheet.  What I am looking for is this.

In Cell J100, I would like the start date of the first winning or losing streak of the season.

In Cell K100, I would like the end date of the first winning or losing streak of the season.

In Cell L100, I would like a current count of that streak.

In Cell M100, I would like the streak identified as a as a "Winning" or "Losing" streak.

The above is easy.  The below is where I cannot figure out how to accomplish in light of the above.

Then in Row 101. I would like to start the process over based on when the above streak ends.  So if the team wins from 3/28 - 3/31 with a streak of 3 games, Row 100 would identify 3/28/2019, 3/31/2019, 3, and Winning.  Then on 4/1 when the team loses, it would start identifying the date 4/1 and a losing streak of 1 and "Losing".

Each Row would continue to change after the team moves from winning to losing or vice versa.

The streak information can be pulled from the cells in AX - AZ 3 - 164 where I have everything in date order and a win/loss columns that will populate according to the results of each game played.

Please see attached.

[#5]

I suggest that you have to place your formulas in helper columns contiguous to your data, similar to the file I attached earlier.