Using Excel Solver to Generate Power Rankings?

[salange]

First, this is as much of a math question as an Excel question, so my apologies for that up front.

I am designing a "power ranking" system that will take the results of a list of games between many dozens of players and rank the players from best to worst. Because each player will have played a different schedule, it is not as easy as simply sorting by wins and losses (if only). Instead, I need the system to account for both a player's record of wins and losses as well as the quality of their opponents. I believe this will require some sort of iterative method (to look at the player's record, and his opponents' records, and their opponents' records, etc.) I am asking for advice regarding implementing a system that I think should work (see below), but if anybody has suggestions for throwing out my system and doing something completely different (including just adapting some existing system) I am all ears.

My method, that I think should work, is modeled after a college football ranking system contained in the "Real Data, No Point Spread" tab of this http://iowahawk.typepad.com/college_fb_ratings/College Football rating.xls spreadsheet. (The games in my tournament will have no "point spread," just a winner and a loser.) My version is available at https://drive.google.com/file/d/0B6_fhgz-r6mbSlowOHVRRnpwSGM/edit?usp=sharing

The method is this:

1- Goal is to calculate "PR." This is the chance of winning a game against an average opponent.
2- Set all players PR equal to .5.
3- On each row, list two opponents, the result of their game, and VLOOKUP their current PR.
4- Calculate the chances of the actual winner winning the game using this formula ( Log5 - Wikipedia, the free encyclopedia ), based on their respective PRs. So, if a .7 beats a .6, the odds of that happening are (.7-(.7*.6))/(.7+.6-(2*.7*.6)) or 61%.
5- Calculate the "error" as the actual result (100% chance of the .7 winning) minus the predicted result (61% chance of the .7 winning), or in this example, 39%. So at this point, we have dozens or hundreds of rows, each of which reflects one game, and a column that gives the "error" for each row/game.
6- In another column, square the errors.
7- In a cell, sum all the squared errors.
8- Use the solver tool to change the PRs until the squared error is minimized.

Sounds great, and it works in that college football spreadsheet I swiped it from above, but my implementation doesn't work for some reason. At first I was running into a divide-by-zero error whenever the two PRs for two opponents were identical, but I avoided that by manually forcing a "50% win chance" result when two equal PRs faced off. But still, I'm getting nonsense results.

I REALIZE THIS IS WAY TOO MURKY TO BE APPEALING FOR ANYONE TO HELP WITH! But nevertheless, I would be most grateful for any advice or thoughts that anyone can offer. If there's a better way to share my spreadsheet so you can see what I'm rambling about, let me know that too.

Thank you!!

 

[shg]

There's a workbook at https://app.box.com/s/40zgcunukurwnn8mdcdw that generates Elo rankings for chess players that does something similar.

 

[salange]

Yes, ELO rankings are similar, but they are different in that they are time dependent. In other words, you end up with a different ELO rating if you have the same ten results against the same ten opponents but in a different order. That doesn't seem like an insurmountable obstacle mathematically, but nevertheless, I can't seem to make mine work.

 

[salange]

Actually, let me amend that. The "time-dependence" of ELO makes it much easier to handle because you simply perform one calculation after the other until you get the end result. Because my need is for something not time-dependent, I think you have to solve everyone's rating "at once," which requires a non-linear tool like the Excel solver (which is where I am running into trouble).

 

[shg]

Rankings are intrinsically time dependent, are they not? If team A loses their first three games, then wins the next 10, does the rating of the team B, which beat them in game 1, go up every round starting in game 5 due to the retrospective adjustment to team A's ranking and therefore the value of the win?

 

[salange]

I suppose that is true, but when I say "not time dependent," what I mean is that I need to create is a system that ranks the teams by looking at all past results, regardless of when they occurred, and weighs them all equally. ELO doesn't work for me, because it effectively weighs recent results more heavily. (In other words, if you face an average team and lose 100 games in a row, and then win 100 games in a row, you end up above-average in ELO. I need for you to end up dead average in that scenario.)

 

[shg]

There's a simple time-independent ranking system described at A very simple ranking system » Pro-football-reference.com blog » Blog Archive that just requires (I think, didn't look too carefully) a matrix inversion.

 

[salange]

That looks very promising. I would need to strip out the "margin-of-victory dependency" of it, but that would seem likely to be easier than adding such a feature in where it doesn't exist. I actually don't have a clue what a matrix inversion is, but there's a handy-dandy how-to video linked in the comments of the post showing how to do it in excel. I'll try to get it set up tonight and see if it works. Thank you, shg!

 

[shg]

You're welcome, good luck.

 

[Tulsa19]

There's a simple time-independent ranking system described at A very simple ranking system » Pro-football-reference.com blog » Blog Archive that just requires (I think, didn't look too carefully) a matrix inversion.

How would you would you build this in a matrix?