Requiring help calculating longest winning streak

Originally Posted by kamran

Hello everyone, I'm in need of some dire help for my assignment which is due tmw. Any help would be greatly appreciated. Heres the scenario:

Team X plays a multiple # of games against 4 different opponents: Team A,B,C, and D.

Here are the results, with the Date that team X played, which team they played against (A,B,C, or D), and whether they won or lost which is donated simply by "1":

Date Team Win Lose
Jan 1 A 1 0
Jan 2 C 1 0
Jan 3 D 0 1
Jan 4 B 1 0
Jan 5 D 1 0
Jan 6 C 1 0
Jan 7 A 1 1
Jan 8 B 0 0
Jan 9 C 0 1
Jan 10 B 0 1
Jan 11 A 0 1
Jan 12 D 1 0



Now my question is, whats a good formula that I could use in Excel to display:

i) the start date and end date of team X's longest winning streak?
ii) the start date and end date of team X's longest losing streak?

Any help would be greatly appreciated.

Hi,

In E2 put\

=IF(D2=1,E1+1,0)

and formula fill that downwards

for F2 test <> 1

hth
---
noted, checking for 'date'

Originally Posted by kamran

Thanks for the reply, I'll try that out.

then you can

=TEXT(OFFSET(A1,(MATCH(MAX(E2:E15),E2:E15,0)-MAX(E2:E15))+1,0),"Mmm-yy")&" to "&TEXT(OFFSET(A1,(MATCH(MAX(E2:E15),E2:E15,0)),0),"Mmm-yy")

---

Originally Posted by kamran

Just one more question: is there a way to return the longest winning streak as single entry. As is, I have a column with my winning streaks. In another language, I would just search the column for the highest number. How does one accomplish this in Excel? Thanks.

did my previous post not give you the longest winning streak, ie Jan 09 - Jan 11

If you mean the highest number of the streak, that is (as shown in that formula) =MAX(E2:E15)

hth
---