Using a formula to calculate Last 10 Games and Win/Loss Streak

**thelogician** · 07-09-2011, 08:10 PM

I've created a workbook for the 2011–12 NHL season so I can calculate standings based on what would happen if the NHL were to change their current standings format of Win-Regulation Loss-Overtime/Shootout Loss to a basic Win-Loss format, where standings are based on Win Percentage instead of Points.

To do this, I've used a workbook of the 2011–12 schedule created by Dirk Hoag of On the Forecheck, where I can enter the results of each game in the sheet labeled "Results", and all the team's stats will be calculated automatically using formulas and reflected in 3 standings tables, on the sheets "League", "Conference" and "Division" (for the entire league standings, the standings of the two conferences, and the standings of the six divisions, respectively). I found formulas to calculate each team's stats, i.e. Wins, Losses, Home/Away Record, Goals For/Against/Difference, etc. from various places on the internet, especially here.

Attached is the workbook, but here's a sample formula, used to calculate the Home Wins for the team whose name appears in B2 on the sheet labeled "League":

=SUMPRODUCT((Results!G$2:G$1231=B2)*(Results!D$2:D$1231<=Results!E$2:E$1231)*(Results!C$2:C$1231<=Results!F$2:F$1231)*(Results!E$2:E$1231<>""))

(Since the season hasn't started, I've entered some hypothetical results to test the formulas.)

The only thing I can't figure out how to calculate are the columns labeled "L10" and "Streak". "L10" means the team's Win-Loss record in their last 10 games, and "Streak" is the team's current Win-Loss streak. Can anyone figure out formulas I can use to calculate these? I only need the formula to be used on the sheet named "League", I can transpose info to use them on the other two sheets. Also, I'd prefer a formula, as I'm not familiar with using VBA. Thanks in advance.

**DonkeyOte** · 07-10-2011, 02:40 AM

The below is put together with a view to amending as little as possible regards your "source" data sheet - as such things are quite complex.

First, create some named ranges... with League!A2 the active cell add the following Defined Names:

Name: _Results
RefersTo: =Results!$A$2:INDEX(Results!$H:$H,MATCH(9.99E+307,Results!$D:$D))

Name: _ResultsFinal
RefersTo: =INDEX(_Results,$R2,1):INDEX(_Results,ROWS(_Results),COLUMNS(_Results))

With the above inserted we can then add some formulae to calculate streak and Last "n" performance.

First we can add a couple of helper cells to assist our calcs...

Q1: Last
Q2:
=IF(LOOKUP(2,1/((INDEX(_Results,0,7)=$B2)+(INDEX(_Results,0,2)=$B2)),SIGN((INDEX(_Results,0,5)+INDEX(_Results,0,6))-(INDEX(_Results,0,4)+INDEX(_Results,0,3)))=LOOKUP(INDEX(_Results,0,7)=$B2,{FALSE,TRUE},{-1,1})),1,-1)
apply custom format to Q2 of "Win";"Loss"
copied down to Q31

R1: Start Ln
R2:
=IF($C2<0+SUBSTITUTE($O$1,"L",""),1,LARGE(IF(ISNUMBER(SEARCH("^"&$B2&"^","^"&INDEX(_Results,0,2)&"^"&INDEX(_Results,0,7)&"^")),ROW(_Results)-ROW(INDEX(_Results,1,1))+1),MIN(SUBSTITUTE($O$1,"L",""),$C2)))
confirmed with CTRL + SHIFT + ENTER
copied down to R31

In simplistic terms:

- Col Q tells us the last result for the given team be it a Win (1) or a Loss (-1)
- Col R establishes from which row of results data set we should begin to look for results (ie discounting those prior to last "n" fixtures)

Then...

L"n" performance:
O2:
=SUM(IF((INDEX(_ResultsFinal,0,7)=$B2)+(INDEX(_ResultsFinal,0,2)=$B2),IF((INDEX(_ResultsFinal,0,5)+INDEX(_ResultsFinal,0,6))>(INDEX(_ResultsFinal,0,4)+INDEX(_ResultsFinal,0,3)),IF(INDEX(_ResultsFinal,0,7)=$B2,1,1/24),IF(INDEX(_ResultsFinal,0,7)=$B2,1/24,1))))
confirmed with CTRL + SHIFT + ENTER
apply custom format of: d-h
copied down to O31

Streak
P2:
=MAX(FREQUENCY(IF((INDEX(_Results,0,7)=$B2)+(INDEX(_Results,0,2)=$B2),IF((SIGN(((INDEX(_Results,0,5)+INDEX(_Results,0,6))-(INDEX(_Results,0,4)+INDEX(_Results,0,3))))=IF(INDEX(_Results,0,7)=$B2,$Q2,-$Q2)),ROW(_Results))),IF((INDEX(_Results,0,7)=$B2)+(INDEX(_Results,0,2)=$B2),IF((SIGN(((INDEX(_Results,0,5)+INDEX(_Results,0,6))-(INDEX(_Results,0,4)+INDEX(_Results,0,3))))<>IF(INDEX(_Results,0,7)=$B2,$Q2,-$Q2)),ROW(_Results)))))*$Q2
confirmed with CTRL + SHIFT + ENTER
apply Custom Format of "W"0;"L"0
copied down to P31

NOTES:

-- Should you modify O1 from L10 to L5 for ex. the calculations will adapt automatically.
-- Streak does not rely on L"n" - it will use all fixtures to determine the streak.

I have attached a working sample of the above.

**thelogician** · 07-10-2011, 09:04 PM

First, thank you so much for your help!

I have some input on your sample. The Streak column seems to work in the opposite way it should. It looks like the formula in the Streak column treats the most recent games as the ones at the top, whereas the most recent games are actually the ones at the bottom. Is this something you can change?

Also, I have inserted two new columns in the Results sheet (see attachment). "V W/L" reflects whether the visiting team won or lost, and "H W/L" reflects whether the home team won or lost. "W" or "L" is displayed in each cell depending on the results of the formula in the cell. I realize this messes up the formulas that you inserted in your attachment, but I was hoping that these new columns would maybe help to calculate L10 and Streak better, by just looking at the "W" or "L" that each team gets in each game and adding up consecutive instances of "W" or "L", maybe making for less complex formulas. Let me know if this helps or not. If not, those two new columns can be deleted.

Next, I feel I should have clarified the scoring format in the "Results" sheet, as it's not one normally used in hockey. Hopefully this will also help in making better formulas if you know how I determine the winner!

Looking at the "Results" sheet, the score is laid out in columns D through G. Column E is the score for the visiting team, and column F is the score for the home team. These reflect the score in both regulation and overtime. If one is greater than the other, then the greater value determines the winning team. If the values are equal, that means the game went to a shootout. Column D shows how many goals the visiting team scored in the shootout, and column G shows the home team's score in the shootout. If the scores in columns E and F are equal (the regulation and overtime score), then the shootout score (columns D and G) determine the winner, with the assumption that either D or G will be greater, since it's the tiebreaker.

Following is the formula I use to determine whether the result for the home team is a "W" or "L" (as listed in cell I2 in the "Results" sheet):

=IF(AND($E2<>"",$F2<>""),IF($E2<$F2,"W",IF($E2>$F2,"L",IF(AND($D2<>"",$G2<>""),IF($D2<$G2,"W",IF($D2>$G2,"L","")),""))),"")

A similar formula is used to determine the result for the visitor:

=IF(AND($E2<>"",$F2<>""),IF($E2>$F2,"W",IF($E2<$F2,"L",IF(AND($D2<>"",$G2<>""),IF($D2>$G2,"W",IF($D2<$G2,"L","")),""))),"")

These formulas check to make sure both the E cell and F cell aren't empty before comparing the game's score, and if the E and F values equal each other, checks to make sure the D and G cells (shootout score) aren't empty before comparing the shootout scores to see which is bigger.

This brings me to another point: Is it possible to redo the formulas for L10 and Streak so that, if all the scores in the "Results" page are empty, it displays a dash? This is just a little nitpicky thing, but I'd like to have the cells in the standings tables not contain error messages if no games have been played.

I hope this isn't far too much work! Thanks again for your help!

**DonkeyOte** · 07-11-2011, 05:07 AM

I am at work presently so can't really review your most recent attachment, however, regards my earlier sample and your points...

Originally Posted by thelogician

The Streak column seems to work in the opposite way it should. It looks like the formula in the Streak column treats the most recent games as the ones at the top, whereas the most recent games are actually the ones at the bottom. Is this something you can change?

The previous frequency calculation was indeed incorrect. The below should work.

P2:
=LOOKUP(9.99E+307,1/(1/FREQUENCY(IF((INDEX(_Results,0,7)=$B2)+(INDEX(_Results,0,2)=$B2),IF((SIGN(((INDEX(_Results,0,5)+INDEX(_Results,0,6))-(INDEX(_Results,0,4)+INDEX(_Results,0,3))))=IF(INDEX(_Results,0,7)=$B2,$Q2,-$Q2)),ROW(_Results))),IF((INDEX(_Results,0,7)=$B2)+(INDEX(_Results,0,2)=$B2),IF((SIGN(((INDEX(_Results,0,5)+INDEX(_Results,0,6))-(INDEX(_Results,0,4)+INDEX(_Results,0,3))))<>IF(INDEX(_Results,0,7)=$B2,$Q2,-$Q2)),ROW(_Results))))))*$Q2
confirmed with CTRL + SHIFT + ENTER
copied down

Originally Posted by thelogician

I feel I should have clarified the scoring format in the "Results" sheet, as it's not one normally used in hockey. Hopefully this will also help in making better formulas if you know how I determine the winner!

Looking at the "Results" sheet, the score is laid out in columns D through G. Column E is the score for the visiting team, and column F is the score for the home team. These reflect the score in both regulation and overtime. If one is greater than the other, then the greater value determines the winning team. If the values are equal, that means the game went to a shootout. Column D shows how many goals the visiting team scored in the shootout, and column G shows the home team's score in the shootout. If the scores in columns E and F are equal (the regulation and overtime score), then the shootout score (columns D and G) determine the winner, with the assumption that either D or G will be greater, since it's teh tiebreaker.

The above is to all intents and purposes irrelevant from a formula perspective.

Put simply the winner is the greater of D + E versus F + G
Given D & G will only be used if E & F are the same adding D & G to E & F respectively does not matter - applying this logic consistently actually simplifies rather than complicates matters.

If you are happy to use "helpers" on Results tab then you should... whether or not the formulae you've added are best suited to the task I can't comment as I can't review your file presently. As and when I have the time I will report back, assuming others do not intervene in the interim of course.

**brokenbiscuits** · 07-11-2011, 05:14 AM

Given that I wouldn't know where to start with amending DonKeyOte's array formulae, I have produced an alternative instead, involving helper colums on the Results tab.

It probably won't work as quickly given the use of INDIRECT, but it works in the interim until Mr Ote comes up with a more elegant solution.

EDIT: I'm getting a database error when I upload for some reason, and it seems like the Don has already beaten me to it anyway!

**thelogician** · 07-13-2011, 05:33 PM

broken,

Do you still get an error in uploading? I'd love to see your alternative.

**gerben69** · 08-03-2013, 09:45 AM

and how would the formula looks like if you won't be counting OT's and you would want to calculate Last 5 games (for example). I would like to know how to calculate last 5 games Win, Draw, Lose, Goals For and Goals Against

**jeffreybrown** · 08-03-2013, 09:51 AM

Hello gerben69 & Welcome to the Forum,

Administrative Note:

Somebody would be happy to help with your query, but first, before we can proceed…
Please see Forum Rule #2...Do not post a question in the thread of another member -- start your own thread..
If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.
Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.