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

Originally Posted by kamran

Ignore the example above. I got a screenshot of how it looks like on my Excel. Note, Column E shows the Winning Streak and Column F shows the Losing Streak.

the person who said 'a picture is worth a thousand words' obviously never tried to deduce what was happening from a screenshot, even one where column A might be deduced.

try the attached, it seems to work even though the range to :15 is two lines overstated.

---

your row 9 ? was a win and a lose? it won't hurt the answer, but mine is either/or, not both.

also, the 10th last character in your formula is a web introduced space, remove all spaces except the " to " spaces.

Hey i just got it!!!... i used your original formula but i changed the "Mmm-yy" part of it to "dd-Mmm" Thats why i kept originally getting Jan-06-Jan-06, since the original formula was just returning the year (yy) "06", the year 2006!!! and when I changed it to "dd-Mmm" it returned the day which is what I wanted. I cant believe that's all that was wrong That formula you gave me was a huuuuuuuuge help bro! Now, I'll be all set to hand in my assignment tomorrow. I could just kiss you! But, I wont cuz im a dude and all. But, I will say this: U R THE MAAAAAAN! Thanks a biggy!!

P.S. sorry about all those other posts across the other boards. Inspite of that, you still helped me out. That shows character. Ur one cool cat !!

Originally Posted by kamran

Hey i just got it!!!... i used your original formula but i changed the "Mmm-yy" part of it to "dd-Mmm" Thats why i kept originally getting Jan-06-Jan-06, since the original formula was just returning the year (yy) "06", the year 2006!!! and when I changed it to "dd-Mmm" it returned the day which is what I wanted. I cant believe that's all that was wrong That formula you gave me was a huuuuuuuuge help bro! Now, I'll be all set to hand in my assignment tomorrow. I could just kiss you! But, I wont cuz im a dude and all. But, I will say this: U R THE MAAAAAAN! Thanks a biggy!!

P.S. sorry about all those other posts across the other boards. Inspite of that, you still helped me out. That shows character. Ur one cool cat !!

Great to see, and yeah, you only need to post once.

Thanks for the response, and good luck with your marks.
---