Calculating current streak and record last 10 games

[wildernessman33]

Hello, we play a lot of foosball here in the office and it has gotten quite competitive. So naturally I needed to set up an ELO ranking spreadsheet so we can keep track of our stats. As we have become more and more serious, we have the need for expanded stats! So I am looking to incorporate a players current streak and their record over the last 10 games (pretty standard stuff for stats keeping).

I know there are several examples of how to do this that can be found across the interwebs, I have yet to find one that works the way I have our spreadsheet set up, and obviously I have been unsuccessful in figuring it out myself. Most spreadsheets I have seen seem to have a column that indicates a win or a loss for a particular team/player. In mine, since foosball is head to head, results are recorded by just putting the winner's name in a column and the loser's name in another.

Here is a link to the spreadsheet so you know what I am talking about:

http://goo.gl/WeWQgR

I was starting by creating an array from columns L and M (L is winner, M is loser) containing only a certain player's name (we will use Dan as an example since he is first on the list). Then once I have an array containing only Dan's games, I could then easily determine the number of times his name appears in column L (wins) and column M (losses) for the last 10 instances. However that is where I am losing it as I can't seem to get the formula put together correctly.

Any help would be appreciated, thanks!

 

[barry houdini]

Hello wildernessman33, welcome to MrExcel

I'm assuming you are adding games at the bottom so the last 10 are the bottom 10, is that right?

Given that then you can use this formula in J3 to give the result "7W" for the last 10 games (and obviously 3L, do you need to show that?)

=COUNTIF(INDEX(L$3:L$1000,LARGE(IF(L$3:M$1000=B3,ROW(L$3:M$1000)-ROW(L$3)+1),10)):L$1000,B3)&"W"

confirm with CTRL+SHIFT+ENTER and copy down

If there aren't 10 games for that player you'll get an error

 

[wildernessman33]

Works like a charm...not I don't need to show losses, hopefully we are all smart enough to know that if you have 7 wins in the last 10 games, that means you have 3 losses.

How about this one...we want to find our nemesis, or the person who has beaten you the most. Basically an array of all games you have lost, then find which name occurs most in the win column (and return that name).

Thanks so much for your help!

 

[barry houdini]

To get the "nemesis" for Dan you can use this formula in row 3

=INDEX(L$3:L$1000,MODE(IF(M$3:M$1000=B3,MATCH(L$3:L$1000,L$3:L$1000,0))))

confirmed with CTRL+SHIFT+ENTER

If two or more players beat Dan an equal number of times then the formula just picks the first of those to do so. You get #N/A if no player has beaten Dan more than once

Edit:

and for "win streak" try this formula in L3

=COUNTIF(INDEX(L$3:L$1000,IFERROR(LARGE(IF(M$3:M$1000=B3,ROW(M$3:M$1000)-ROW(M$3)+1),1),1)):L$1000,B3)&"W"

confirmed with CTRL+SHIFT+ENTER and copied down

All but Dan have a win streak of 0W currently because their last games were all losses

 

[wildernessman33]

Thanks so much for your help, I am going try to get the formulas for any new stat categories on my own, should they come up.

 

[statmanml]

I have tried to figure out the same thing for my own personal pro sports stats, but couldn't. Thanks for the info, but I have had trouble viewing the spreadsheet in your post. Is there a way to repost it or a different way to view it? Thanks in advance!