Winning Streaks or Loosing Streaks in multi team football schedule

[hammerb]

I have been searching for a solution to my problem for quite a long time. I have read other posts from other people with a similar problem however i have not found a way to take their result and adjust it to fit my own needs.

I have constructed a 5 team football schedule that I will input the final score from every game on a weekly basis and from that my Spreadsheet will compute weather the team won or lost, each teams individual will/loss record, the win/loss record for every game played every week, each teams winning percentage, all 5 teams combined winning percentage, number of games remaining, number of games remaining if said team earns a playoff birth, best possible win/loss record based on their current win/loss record, & worst possible win/loss record based on their current win/loss record.

Now that i have figured out all of that I am wanting to add 4 more formulas to each of my 5 teams.

1. I want to see each teams current winning/loosing streak
2. I want to see each teams current winning/loosing streak for home games
3. I want to see each teams current winning/loosing streak for games away from home (away games and neutral site games, not including playoff games)
4. I want to see each teams current winning/loosing streak for post season games (playoff games)

I have scoured the web trying to find other peoples solutions and turning them into what I need but i have been unable to do that. Winning/loosing streaks seem to be extremely difficult to produce.

Here is a copy of my spreadsheet:

http://dl.dropbox.com/u/13660908/2011 Week Progress 2.xls
(I will also attempt to attach it below)

Before you download it please note: The only input that is required to make everything work is to simply add the final score for each game (my team on the left and opposing team on the right) everything that I mentioned in the second paragraph of this post is computed automatically.

Please take a look at sheet2 and sheet3 for additional info and what I refer to as "ugly cells" or cells that do a lot of the computing however i choose to keep them out of sight and out of mind. As you can see i am not a huge fan of hiding cells.

Near the bottom of Sheet1 you will see the four locations for the streaks. Until now I have simply figured these manually and would like for these to be computed automatically if possible.

Any help you can provide will be greatly appreciated.

 

[How_Do_I]

Have a look at the following... You have the results of the matches in Column A. (W = Win, D = Draw and L = Loss)... Note the order, I have the last game played at the top of that column and the oldest game at the bottom... Let me know if you can't work out how to do that...

Then the formulas in my Jeanie will work... You might not want the formula that sorts out a singular defeat as opposed to a streak of 2 losses which then uses defeats i.e. plural...

You would need to do this for home games, then away games and overall games. So Column A might be home games... Column X away games and Column whatever overall.

Excel Workbook
	A	B	C	D	E	F	G
3	D	Last Game
4	W	2nd to last game			Home
5	L				1 Draw	D	1
6
7					W	1
8					D	1
9					L	1
10
11
12
13
14
15
16
17
18
19
20
Sheet1

 

[hammerb]

My problem is that the last game played is at the bottom of the list and sometimes i have OPEN weeks in the schedule that cause problems.

example: If a team has two wins then an open week then another win then this appears as a streak of 1 Win. When It should be a streak of 3 wins.

Is there any way to "flip" the formula so that the previous game played is at the bottom of the list?

 

[How_Do_I]

I only know the way I've shown you... I just put my scores into my workbook... And excel sorts it out... I don't understand an "open week" as we are talking games with different shaped balls!... To get my information I use different worksheets to calculate and then "send" the data to "presentation sheets"...

I'm trying to say, could you use a different sheet to reverse everything and then get the data you need "sent" to another sheet?

 

[hammerb]

Open Week = aka, a bye week in the NFL; an opening in the schedule, no games scheduled, an off day in MLB, and Open Week in NCAA Football

 

[T. Valko]

Open Week = aka, a bye week in the NFL; an opening in the schedule, no games scheduled, an off day in MLB, and Open Week in NCAA Football

I didn't look at your file...

Maybe this will get you headed in the right direction.

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Verdana,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=0><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 45px"><COL style="WIDTH: 45px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">L</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">4 L</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">W</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">L</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">L</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">L</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">L</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">W</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">L</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">L</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">L</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">15</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">L</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD></TR></TBODY></TABLE>


This array formula** entered in B2 for the current streak:

=LOOKUP(1E100,FREQUENCY(IF(A2:A15=LOOKUP("zzz",A2:A15),ROW(A2:A15)),IF(A2:A15<>LOOKUP("zzz",A2:A15),IF(A2:A15<>"",ROW(A2:A15)))))&" "&LOOKUP("zzz",A2:A15)

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

That looks like the Pirates record!

 

[How_Do_I]

Hi Biff,

I liked your formula, is there a way of making it look for the streaks with the way I have my workbook setup... i.e. last match played at the top and oldest match played at the bottom?

 

[T. Valko]

Hi Biff,

I liked your formula, is there a way of making it look for the streaks with the way I have my workbook setup... i.e. last match played at the top and oldest match played at the bottom?

Like this...

Book1
	A	B
2	W	6 W
3	W	_
4	W	_
5	W	_
6	_	_
7	_	_
8	_	_
9	W	_
10	W	_
11	L	_
12	W	_
13	W	_
14	L	_
15	W	_
Sheet1

This array formula entered in B2:

=FREQUENCY(IF(A2:A15=INDEX(A2:A15,MATCH("*",A2:A15,0)),ROW(A2:A15)),IF(A2:A15<>INDEX(A2:A15,MATCH("*",A2:A15,0)),IF(A2:A15<>"",ROW(A2:A15))))&" "&INDEX(A2:A15,MATCH("*",A2:A15,0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[hammerb]

Thank you very much. I have an additional question. I managed to get the formula you gave me (oldest game on top and most recent game on bottom) to work. However What can I do if my team is only half way through the season and the bottom half of my W/L column is empty. Your formula is functioning properly except when the season is not complete yet I get a number representing the current streak and a blank (where the "W" or "L" would normally be).

Is there any way that I could adjust the formula to support showing the winning/loosing streak for a season that is nto completed yet?

 

[How_Do_I]

Biff...

Thanks for your formula.