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.

 

[T. Valko]

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?

Sounds like you have formulas in the cells and some of them at the bottom return formula blanks or some other type of unseen whitespace character.

Replace each instance of:

LOOKUP("zzz",A2:A15)

With:

LOOKUP(2,1/(A2:A15<>""),A2:A15)

However, that will make the eentire formula a tad on the long side. What you could do is enter the above replacement formula in a cell and then refer to that cell:

Z2: =LOOKUP(2,1/(A2:A15<>""),A2:A15)

Then (still array entered):

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

 

[T. Valko]

Biff...

Thanks for your formula.

You're welcome!

 

[hammerb]

absolutely fantastic. I don't think I ever could have figured this out on my own. I have one more question for you:

What about doing a winning/losing streak that spans multiple seasons? Is there a way that I could use your formula to add last years winning streak to this years by entering a number into a specified cell. Example since one team finished last season with a 5 game winning streak and is currently on a 4 game winning streak this year then could I use your formula to figure this seasons winning streak and simply add 5 to it?

 

[T. Valko]

absolutely fantastic. I don't think I ever could have figured this out on my own. I have one more question for you:

What about doing a winning/losing streak that spans multiple seasons? Is there a way that I could use your formula to add last years winning streak to this years by entering a number into a specified cell. Example since one team finished last season with a 5 game winning streak and is currently on a 4 game winning streak this year then could I use your formula to figure this seasons winning streak and simply add 5 to it?

I don't see why you couldn't do that.

You'd have to see what the last entry was for last year and if it's the same as the last entry for the current year then you'd just add the 2 numbers together.

Tell me where all the relavant data is and I'll come up with the formula.

 

[hammerb]

I have:

=LOOKUP(1E100,FREQUENCY(IF(A2:A20=Z2,ROW(A2:A20)),IF(A2:A20<>Z2,IF(A2:A20<>"",ROW(A2:A20)))))&" "&Z2 in cell B2

I have:

=LOOKUP(2,1/(A2:A15<>""),A2:A15) in cell Z2

I have:

my column of W's and L's from A2:A20

I have:

the previous seasons streak in cell B1

(example: The Kansas City Chiefs finished last season with a 2 game losing streak so B1 has "2 L" in it (without the quotes))

This cell is simply manually entered and is not the product of a formula or anything

What I was wanting to see (if possible) would be for your formula to take that streak into account if it is still a part of the current streak

(example: since Kansas City finished last season with 2 losses they have a 2 L in cell B1 and they lost the first three games of this season so I was hoping that your formula in Cell B2 would take these two cell into account together and produce a 5 L in cell B2

I thank you so very much

 

[T. Valko]

I have:

=LOOKUP(1E100,FREQUENCY(IF(A2:A20=Z2,ROW(A2:A20)),IF(A2:A20<>Z2,IF(A2:A20<>"",ROW(A2:A20)))))&" "&Z2 in cell B2

I have:

=LOOKUP(2,1/(A2:A15<>""),A2:A15) in cell Z2

I have:

my column of W's and L's from A2:A20

I have:

the previous seasons streak in cell B1

(example: The Kansas City Chiefs finished last season with a 2 game losing streak so B1 has "2 L" in it (without the quotes))

This cell is simply manually entered and is not the product of a formula or anything

What I was wanting to see (if possible) would be for your formula to take that streak into account if it is still a part of the current streak

(example: since Kansas City finished last season with 2 losses they have a 2 L in cell B1 and they lost the first three games of this season so I was hoping that your formula in Cell B2 would take these two cell into account together and produce a 5 L in cell B2

I thank you so very much

Ok, make sure all the ranges are the same. The formula for Z2 is using a smaller range than the formula in B2.

Still array entered:

=LOOKUP(1E100,FREQUENCY(IF(A2:A20=Z2,ROW(A2:A20)),IF(A2:A20<>Z2,IF(A2:A20<>"",ROW(A2:A20)))))+IF(RIGHT(B1)=Z2,LEFT(B1,2))&" "&Z2

 

[hammerb]

Thank you very much. You have helped me a lot. Everything on my spreadsheet looks fantastic.

 

[T. Valko]

Thank you very much. You have helped me a lot. Everything on my spreadsheet looks fantastic.

You're welcome. Thanks for the feedback!

 

[falconer81]

I didn't look at your file...

Maybe this will get you headed in the right direction.

Sheet1

	A	B

<colgroup><col style="FONT-WEIGHT: bold; WIDTH: 30px"><col style="WIDTH: 45px"><col style="WIDTH: 45px"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]4 L[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: center"]W[/TD]
[TD="align: center"]_[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]_[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]_[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]_[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: center"]_[/TD]
[TD="align: center"]_[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]_[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="align: center"]W[/TD]
[TD="align: center"]_[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]_[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]
[TD="align: center"]_[/TD]
[TD="align: center"]_[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]_[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]_[/TD]

[TD="bgcolor: #cacaca, align: center"]14[/TD]
[TD="align: center"]_[/TD]
[TD="align: center"]_[/TD]

[TD="bgcolor: #cacaca, align: center"]15[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]_[/TD]

</tbody>

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!

Hello! I have a problem with this. I don't have a helper column (a2:a15 in this case) just the results from a soccer league. Range a2:a15 is replace by an array which is counted other formulas?
Thanks!