I was asked to share my spreadsheet power rankings system with you guys. I may eventually hand it off to one of you once I feel that it is working properly. I run this spreadsheet for all the league I have players in. Anyways, here is how these power rankings work. Keep in mind that things can change and constructive feedback can help make it better.
First off, all the team stats are copy/pasted into a spreadsheet and sorted. Then I fill out a matrix to identify which teams have played one another and which team won. Then all this is passed to the first set of matrices which calculates various things about your opponents and basically creates a bunch of averages. This is where I figure out how good your opponents are.
The data from these matrices are passed to a common sheet for all the data I am interested in. A few more things are calculated here (percentages). After this, data is passed to the second set of matrices which simulate out various parts of the season. Things like how many yards you would gain or points allowed. The setup is based on this league projector: http://goallineblitz.com/game/forum_thread.pl?thread_id=447611
The third set of matrices brings everything together and does a comparative analysis to determine who would win a head to head matchup if everyone played all 31 other teams. It then reports to a matrix similar to the one I use to input who won or lost. I then have this matrix dump the wins, losses, and ties into the common sheet. The team with the most wins gets the highest power rank and so on down.
What is taken into account in the determination of who won or lost is this:
-predicted points scored and allowed
-how likely you are to win based on record quality (i.e. who is more likely to beat good teams and not lose to bad ones)
-predicted yards gained and allowed
-predicted amount of turnovers for and against
This is the formula so far
Your points = 14pts*percent chance of win based on record + the points you are predicted to score + predicted yards / 14 + predicted turnover ratio x 7
Your opponent gets the same formula. The team with the higher number of points wins that game.
I also put offense and defense ranks on there. Right now they are based on the predicted number of yards.
=====================================================================
Week 1 (I suspect once the next set of data gets in the spreadsheet won't be as impressed by some of these blowout victories.)
Rank ..... Prv ... Offense ....... Defense ....... Team
............................. P/R ................ P/R
Lion
... 01 ...... NA ..... 17 / 02 ......... 28 / 01 .... Burundi Hutu
... 05 ...... NA ..... 09 / 06 ......... 05 / 10 .... Beltway Bandits
... 06 ...... NA ..... 19 / 03 ......... 18 / 04 .... Atlantic Blue Jays
... 08 ...... NA ..... 13 / 12 ......... 09 / 05 .... Cape Verde Headhunters
... 09 ...... NA ..... 04 / 05 ......... 22 / 26 .... Tijuana Thundercats
... 10 ...... NA ..... 01 / 15 ......... 12 / 09 .... Mozambique Starvin Marvins
... 10 ...... NA ..... 16 / 21 ......... 27 / 08 .... Red Sea Wave
... 15 ...... NA ..... 14 / 18 ......... 07 / 07 .... Scottish Sharks
... 21 ...... NA ..... 06 / 25 ......... 17 / 12 .... Katanga Diamonds
... 22 ...... NA ..... 24 / 28 ......... 20 / 21 .... Band of Brothers
... 22 ...... NA ..... 21 / 24 ......... 32 / 18 .... Oklahoma Fire
... 22 ...... NA ..... 26 / 26 ......... 19 / 15 .... Ethiopia Executioners
... 26 ...... NA ..... 11 / 07 ......... 29 / 28 .... A.C.T Orcas
... 27 ...... NA ..... 28 / 23 ......... 24 / 27 .... Rhein Main Magic Mushrooms
... 28 ...... NA ..... 15 / 29 ......... 14 / 30 .... River City Riot
... 32 ...... NA ..... 05 / 32 ......... 16 / 31 .... Johannesburg Impala's
Elephant
... 02 ...... NA ..... 31 / 01 ......... 03 / 02 .... Ghana Buffalos
... 03 ...... NA ..... 27 / 04 ......... 23 / 03 .... Cairo Crocodiles
... 04 ...... NA ..... 12 / 09 ......... 04 / 11 .... Sierra Leone Magicians
... 07 ...... NA ..... 07 / 10 ......... 25 / 14 .... Cape Town Shinobis
... 10 ...... NA ..... 25 / 14 ......... 10 / 22 .... Senegal NATIONALS
... 13 ...... NA ..... 18 / 20 ......... 31 / 06 .... Kinshasa Rumblers
... 13 ...... NA ..... 03 / 16 ......... 01 / 25 .... Togo Strikers
... 16 ...... NA ..... 20 / 13 ......... 11 / 16 .... Zanzibar Leopards
... 17 ...... NA ..... 22 / 17 ......... 13 / 20 .... Doinitin Djibouti
... 18 ...... NA ..... 32 / 08 ......... 30 / 17 .... Kigali Silverbacks
... 19 ...... NA ..... 02 / 27 ......... 15 / 13 .... Rwanda Assassins
... 20 ...... NA ..... 23 / 11 ......... 08 / 19 .... Zimbabwe Black Rhinos
... 25 ...... NA ..... 08 / 19 ......... 26 / 23 .... Nairobi Night Hawks
... 29 ...... NA ..... 10 / 30 ......... 06 / 29 .... Somalia Whales
... 30 ...... NA ..... 29 / 22 ......... 21 / 24 .... Casablanca White Houses
... 31 ...... NA ..... 30 / 31 ......... 02 / 32 .... R.I.P. Phantoms
================================================================================================
Week 2 (I changed the record part of the formula to predict the percentage chance you have to win that game based on record quality alone, this means instead of one team getting all 14 points the two teams split it based upon strength of their record. If two teams have the same record strength they each get 50% or 7 points and the simulation decides based on points, yards, and turnovers).
Rank ..... Prv ... Offense ....... Defense ....... Team
............................. P/R ................ P/R
Lion
... 04 ...... 08 ..... 12 / 08 ......... 05 / 09 .... Cape Verde Headhunters
... 05 ...... 10 ..... 03 / 15 ......... 10 / 05 .... Mozambique Starvin Marvins
... 06 ...... 10 ..... 17 / 04 ......... 22 / 04 .... Red Sea Wave
... 07 ...... 01 ..... 27 / 02 ......... 25 / 28 .... Burundi Hutu
... 12 ...... 22 ..... 14 / 27 ......... 14 / 15 .... Band of Brothers
... 14 ...... 09 ..... 15 / 09 ......... 15 / 26 .... Tijuana Thundercats
... 15 ...... 06 ..... 16 / 13 ......... 20 / 18 .... Atlantic Blue Jays
... 15 ...... 26 ..... 04 / 14 ......... 30 / 22 .... A.C.T Orcas
... 17 ...... 05 ..... 06 / 12 ......... 27 / 08 .... Beltway Bandits
... 19 ...... 15 ..... 18 / 19 ......... 12 / 10 .... Scottish Sharks
... 22 ...... 21 ..... 11 / 20 ......... 09 / 01 .... Katanga Diamonds
... 23 ...... 22 ..... 29 / 11 ......... 17 / 14 .... Ethiopia Executioners
... 24 ...... 22 ..... 23 / 25 ......... 23 / 24 .... Oklahoma Fire
... 27 ...... 27 ..... 32 / 17 ......... 18 / 29 .... Rhein Main Magic Mushrooms
... 29 ...... 28 ..... 19 / 30 ......... 06 / 30 .... River City Riot
... 32 ...... 32 ..... 08 / 32 ......... 19 / 32 .... Johannesburg Impala's
Elephant
... 01 ...... 02 ..... 24 / 03 ......... 02 / 03 .... Ghana Buffalos
... 02 ...... 04 ..... 07 / 10 ......... 03 / 13 .... Sierra Leone Magicians
... 03 ...... 16 ..... 01 / 01 ......... 31 / 07 .... Zanzibar Leopards
... 07 ...... 03 ..... 22 / 06 ......... 21 / 02 .... Cairo Crocodiles
... 09 ...... 17 ..... 20 / 07 ......... 07 / 12 .... Doinitin Djibouti
... 10 ...... 13 ..... 05 / 16 ......... 01 / 19 .... Togo Strikers
... 10 ...... 18 ..... 28 / 05 ......... 16 / 06 .... Kigali Silverbacks
... 12 ...... 13 ..... 02 / 26 ......... 29 / 11 .... Kinshasa Rumblers
... 18 ...... 07 ..... 21 / 22 ......... 24 / 21 .... Cape Town Shinobis
... 19 ...... 10 ..... 25 / 24 ......... 26 / 20 .... Senegal NATIONALS
... 19 ...... 19 ..... 10 / 18 ......... 11 / 16 .... Rwanda Assassins
... 25 ...... 20 ..... 30 / 23 ......... 08 / 17 .... Zimbabwe Black Rhinos
... 26 ...... 25 ..... 09 / 28 ......... 28 / 25 .... Nairobi Night Hawks
... 28 ...... 30 ..... 26 / 21 ......... 32 / 23 .... Casablanca White Houses
... 30 ...... 29 ..... 13 / 29 ......... 13 / 27 .... Somalia Whales
... 31 ...... 31 ..... 31 / 31 ......... 04 / 31 .... R.I.P. Phantoms
=================================================================================================
Week 3: Ghana stays on top only to be joined by Mozambique. No changes to the spreadsheet since week 2.
Rank ..... Prv ... Offense ....... Defense ....... Team
............................. P/R ................ P/R
Lion
... 01 ...... 05 ..... 08 / 08 ......... 16 / 01 .... Mozambique Starvin Marvins
... 03 ...... 04 ..... 11 / 09 ......... 04 / 09 .... Cape Verde Headhunters
... 03 ...... 06 ..... 14 / 04 ......... 23 / 04 .... Red Sea Wave
... 06 ...... 14 ..... 16 / 02 ......... 27 / 22 .... Tijuana Thundercats
... 08 ...... 07 ..... 21 / 03 ......... 15 / 15 .... Burundi Hutu
... 10 ...... 15 ..... 06 / 10 ......... 29 / 18 .... A.C.T Orcas
... 12 ...... 12 ..... 13 / 27 ......... 13 / 16 .... Band of Brothers
... 14 ...... 15 ..... 23 / 11 ......... 18 / 12 .... Atlantic Blue Jays
... 16 ...... 17 ..... 02 / 15 ......... 24 / 07 .... Beltway Bandits
... 20 ...... 19 ..... 17 / 24 ......... 12 / 14 .... Scottish Sharks
... 22 ...... 23 ..... 29 / 14 ......... 11 / 20 .... Ethiopia Executioners
... 22 ...... 24 ..... 20 / 26 ......... 30 / 24 .... Oklahoma Fire
... 24 ...... 22 ..... 19 / 17 ......... 21 / 02 .... Katanga Diamonds
... 28 ...... 27 ..... 32 / 22 ......... 14 / 29 .... Rhein Main Magic Mushrooms
... 30 ...... 29 ..... 15 / 31 ......... 05 / 30 .... River City Riot
... 32 ...... 32 ..... 05 / 32 ......... 07 / 32 .... Johannesburg Impala's
Elephant
... 01 ...... 01 ..... 26 / 01 ......... 01 / 03 .... Ghana Buffalos
... 03 ...... 07 ..... 24 / 06 ......... 10 / 05 .... Cairo Crocodiles
... 07 ...... 03 ..... 03 / 05 ......... 31 / 13 .... Zanzibar Leopards
... 09 ...... 02 ..... 10 / 16 ......... 02 / 17 .... Sierra Leone Magicians
... 10 ...... 12 ..... 04 / 18 ......... 28 / 10 .... Kinshasa Rumblers
... 12 ...... 09 ..... 22 / 12 ......... 26 / 08 .... Doinitin Djibouti
... 14 ...... 10 ..... 30 / 07 ......... 19 / 06 .... Kigali Silverbacks
... 16 ...... 10 ..... 07 / 13 ......... 03 / 21 .... Togo Strikers
... 16 ...... 19 ..... 01 / 21 ......... 09 / 11 .... Rwanda Assassins
... 19 ...... 18 ..... 12 / 19 ......... 17 / 26 .... Cape Town Shinobis
... 21 ...... 19 ..... 28 / 25 ......... 22 / 23 .... Senegal NATIONALS
... 24 ...... 25 ..... 27 / 20 ......... 06 / 19 .... Zimbabwe Black Rhinos
... 26 ...... 26 ..... 09 / 28 ......... 25 / 25 .... Nairobi Night Hawks
... 27 ...... 28 ..... 25 / 23 ......... 32 / 27 .... Casablanca White Houses
... 29 ...... 30 ..... 18 / 29 ......... 20 / 28 .... Somalia Whales
... 31 ...... 31 ..... 31 / 30 ......... 08 / 31 .... R.I.P. Phantoms
First off, all the team stats are copy/pasted into a spreadsheet and sorted. Then I fill out a matrix to identify which teams have played one another and which team won. Then all this is passed to the first set of matrices which calculates various things about your opponents and basically creates a bunch of averages. This is where I figure out how good your opponents are.
The data from these matrices are passed to a common sheet for all the data I am interested in. A few more things are calculated here (percentages). After this, data is passed to the second set of matrices which simulate out various parts of the season. Things like how many yards you would gain or points allowed. The setup is based on this league projector: http://goallineblitz.com/game/forum_thread.pl?thread_id=447611
The third set of matrices brings everything together and does a comparative analysis to determine who would win a head to head matchup if everyone played all 31 other teams. It then reports to a matrix similar to the one I use to input who won or lost. I then have this matrix dump the wins, losses, and ties into the common sheet. The team with the most wins gets the highest power rank and so on down.
What is taken into account in the determination of who won or lost is this:
-predicted points scored and allowed
-how likely you are to win based on record quality (i.e. who is more likely to beat good teams and not lose to bad ones)
-predicted yards gained and allowed
-predicted amount of turnovers for and against
This is the formula so far
Your points = 14pts*percent chance of win based on record + the points you are predicted to score + predicted yards / 14 + predicted turnover ratio x 7
Your opponent gets the same formula. The team with the higher number of points wins that game.
I also put offense and defense ranks on there. Right now they are based on the predicted number of yards.
=====================================================================
Week 1 (I suspect once the next set of data gets in the spreadsheet won't be as impressed by some of these blowout victories.)
Rank ..... Prv ... Offense ....... Defense ....... Team
............................. P/R ................ P/R
Lion
... 01 ...... NA ..... 17 / 02 ......... 28 / 01 .... Burundi Hutu
... 05 ...... NA ..... 09 / 06 ......... 05 / 10 .... Beltway Bandits
... 06 ...... NA ..... 19 / 03 ......... 18 / 04 .... Atlantic Blue Jays
... 08 ...... NA ..... 13 / 12 ......... 09 / 05 .... Cape Verde Headhunters
... 09 ...... NA ..... 04 / 05 ......... 22 / 26 .... Tijuana Thundercats
... 10 ...... NA ..... 01 / 15 ......... 12 / 09 .... Mozambique Starvin Marvins
... 10 ...... NA ..... 16 / 21 ......... 27 / 08 .... Red Sea Wave
... 15 ...... NA ..... 14 / 18 ......... 07 / 07 .... Scottish Sharks
... 21 ...... NA ..... 06 / 25 ......... 17 / 12 .... Katanga Diamonds
... 22 ...... NA ..... 24 / 28 ......... 20 / 21 .... Band of Brothers
... 22 ...... NA ..... 21 / 24 ......... 32 / 18 .... Oklahoma Fire
... 22 ...... NA ..... 26 / 26 ......... 19 / 15 .... Ethiopia Executioners
... 26 ...... NA ..... 11 / 07 ......... 29 / 28 .... A.C.T Orcas
... 27 ...... NA ..... 28 / 23 ......... 24 / 27 .... Rhein Main Magic Mushrooms
... 28 ...... NA ..... 15 / 29 ......... 14 / 30 .... River City Riot
... 32 ...... NA ..... 05 / 32 ......... 16 / 31 .... Johannesburg Impala's
Elephant
... 02 ...... NA ..... 31 / 01 ......... 03 / 02 .... Ghana Buffalos
... 03 ...... NA ..... 27 / 04 ......... 23 / 03 .... Cairo Crocodiles
... 04 ...... NA ..... 12 / 09 ......... 04 / 11 .... Sierra Leone Magicians
... 07 ...... NA ..... 07 / 10 ......... 25 / 14 .... Cape Town Shinobis
... 10 ...... NA ..... 25 / 14 ......... 10 / 22 .... Senegal NATIONALS
... 13 ...... NA ..... 18 / 20 ......... 31 / 06 .... Kinshasa Rumblers
... 13 ...... NA ..... 03 / 16 ......... 01 / 25 .... Togo Strikers
... 16 ...... NA ..... 20 / 13 ......... 11 / 16 .... Zanzibar Leopards
... 17 ...... NA ..... 22 / 17 ......... 13 / 20 .... Doinitin Djibouti
... 18 ...... NA ..... 32 / 08 ......... 30 / 17 .... Kigali Silverbacks
... 19 ...... NA ..... 02 / 27 ......... 15 / 13 .... Rwanda Assassins
... 20 ...... NA ..... 23 / 11 ......... 08 / 19 .... Zimbabwe Black Rhinos
... 25 ...... NA ..... 08 / 19 ......... 26 / 23 .... Nairobi Night Hawks
... 29 ...... NA ..... 10 / 30 ......... 06 / 29 .... Somalia Whales
... 30 ...... NA ..... 29 / 22 ......... 21 / 24 .... Casablanca White Houses
... 31 ...... NA ..... 30 / 31 ......... 02 / 32 .... R.I.P. Phantoms
================================================================================================
Week 2 (I changed the record part of the formula to predict the percentage chance you have to win that game based on record quality alone, this means instead of one team getting all 14 points the two teams split it based upon strength of their record. If two teams have the same record strength they each get 50% or 7 points and the simulation decides based on points, yards, and turnovers).
Rank ..... Prv ... Offense ....... Defense ....... Team
............................. P/R ................ P/R
Lion
... 04 ...... 08 ..... 12 / 08 ......... 05 / 09 .... Cape Verde Headhunters
... 05 ...... 10 ..... 03 / 15 ......... 10 / 05 .... Mozambique Starvin Marvins
... 06 ...... 10 ..... 17 / 04 ......... 22 / 04 .... Red Sea Wave
... 07 ...... 01 ..... 27 / 02 ......... 25 / 28 .... Burundi Hutu
... 12 ...... 22 ..... 14 / 27 ......... 14 / 15 .... Band of Brothers
... 14 ...... 09 ..... 15 / 09 ......... 15 / 26 .... Tijuana Thundercats
... 15 ...... 06 ..... 16 / 13 ......... 20 / 18 .... Atlantic Blue Jays
... 15 ...... 26 ..... 04 / 14 ......... 30 / 22 .... A.C.T Orcas
... 17 ...... 05 ..... 06 / 12 ......... 27 / 08 .... Beltway Bandits
... 19 ...... 15 ..... 18 / 19 ......... 12 / 10 .... Scottish Sharks
... 22 ...... 21 ..... 11 / 20 ......... 09 / 01 .... Katanga Diamonds
... 23 ...... 22 ..... 29 / 11 ......... 17 / 14 .... Ethiopia Executioners
... 24 ...... 22 ..... 23 / 25 ......... 23 / 24 .... Oklahoma Fire
... 27 ...... 27 ..... 32 / 17 ......... 18 / 29 .... Rhein Main Magic Mushrooms
... 29 ...... 28 ..... 19 / 30 ......... 06 / 30 .... River City Riot
... 32 ...... 32 ..... 08 / 32 ......... 19 / 32 .... Johannesburg Impala's
Elephant
... 01 ...... 02 ..... 24 / 03 ......... 02 / 03 .... Ghana Buffalos
... 02 ...... 04 ..... 07 / 10 ......... 03 / 13 .... Sierra Leone Magicians
... 03 ...... 16 ..... 01 / 01 ......... 31 / 07 .... Zanzibar Leopards
... 07 ...... 03 ..... 22 / 06 ......... 21 / 02 .... Cairo Crocodiles
... 09 ...... 17 ..... 20 / 07 ......... 07 / 12 .... Doinitin Djibouti
... 10 ...... 13 ..... 05 / 16 ......... 01 / 19 .... Togo Strikers
... 10 ...... 18 ..... 28 / 05 ......... 16 / 06 .... Kigali Silverbacks
... 12 ...... 13 ..... 02 / 26 ......... 29 / 11 .... Kinshasa Rumblers
... 18 ...... 07 ..... 21 / 22 ......... 24 / 21 .... Cape Town Shinobis
... 19 ...... 10 ..... 25 / 24 ......... 26 / 20 .... Senegal NATIONALS
... 19 ...... 19 ..... 10 / 18 ......... 11 / 16 .... Rwanda Assassins
... 25 ...... 20 ..... 30 / 23 ......... 08 / 17 .... Zimbabwe Black Rhinos
... 26 ...... 25 ..... 09 / 28 ......... 28 / 25 .... Nairobi Night Hawks
... 28 ...... 30 ..... 26 / 21 ......... 32 / 23 .... Casablanca White Houses
... 30 ...... 29 ..... 13 / 29 ......... 13 / 27 .... Somalia Whales
... 31 ...... 31 ..... 31 / 31 ......... 04 / 31 .... R.I.P. Phantoms
=================================================================================================
Week 3: Ghana stays on top only to be joined by Mozambique. No changes to the spreadsheet since week 2.
Rank ..... Prv ... Offense ....... Defense ....... Team
............................. P/R ................ P/R
Lion
... 01 ...... 05 ..... 08 / 08 ......... 16 / 01 .... Mozambique Starvin Marvins
... 03 ...... 04 ..... 11 / 09 ......... 04 / 09 .... Cape Verde Headhunters
... 03 ...... 06 ..... 14 / 04 ......... 23 / 04 .... Red Sea Wave
... 06 ...... 14 ..... 16 / 02 ......... 27 / 22 .... Tijuana Thundercats
... 08 ...... 07 ..... 21 / 03 ......... 15 / 15 .... Burundi Hutu
... 10 ...... 15 ..... 06 / 10 ......... 29 / 18 .... A.C.T Orcas
... 12 ...... 12 ..... 13 / 27 ......... 13 / 16 .... Band of Brothers
... 14 ...... 15 ..... 23 / 11 ......... 18 / 12 .... Atlantic Blue Jays
... 16 ...... 17 ..... 02 / 15 ......... 24 / 07 .... Beltway Bandits
... 20 ...... 19 ..... 17 / 24 ......... 12 / 14 .... Scottish Sharks
... 22 ...... 23 ..... 29 / 14 ......... 11 / 20 .... Ethiopia Executioners
... 22 ...... 24 ..... 20 / 26 ......... 30 / 24 .... Oklahoma Fire
... 24 ...... 22 ..... 19 / 17 ......... 21 / 02 .... Katanga Diamonds
... 28 ...... 27 ..... 32 / 22 ......... 14 / 29 .... Rhein Main Magic Mushrooms
... 30 ...... 29 ..... 15 / 31 ......... 05 / 30 .... River City Riot
... 32 ...... 32 ..... 05 / 32 ......... 07 / 32 .... Johannesburg Impala's
Elephant
... 01 ...... 01 ..... 26 / 01 ......... 01 / 03 .... Ghana Buffalos
... 03 ...... 07 ..... 24 / 06 ......... 10 / 05 .... Cairo Crocodiles
... 07 ...... 03 ..... 03 / 05 ......... 31 / 13 .... Zanzibar Leopards
... 09 ...... 02 ..... 10 / 16 ......... 02 / 17 .... Sierra Leone Magicians
... 10 ...... 12 ..... 04 / 18 ......... 28 / 10 .... Kinshasa Rumblers
... 12 ...... 09 ..... 22 / 12 ......... 26 / 08 .... Doinitin Djibouti
... 14 ...... 10 ..... 30 / 07 ......... 19 / 06 .... Kigali Silverbacks
... 16 ...... 10 ..... 07 / 13 ......... 03 / 21 .... Togo Strikers
... 16 ...... 19 ..... 01 / 21 ......... 09 / 11 .... Rwanda Assassins
... 19 ...... 18 ..... 12 / 19 ......... 17 / 26 .... Cape Town Shinobis
... 21 ...... 19 ..... 28 / 25 ......... 22 / 23 .... Senegal NATIONALS
... 24 ...... 25 ..... 27 / 20 ......... 06 / 19 .... Zimbabwe Black Rhinos
... 26 ...... 26 ..... 09 / 28 ......... 25 / 25 .... Nairobi Night Hawks
... 27 ...... 28 ..... 25 / 23 ......... 32 / 27 .... Casablanca White Houses
... 29 ...... 30 ..... 18 / 29 ......... 20 / 28 .... Somalia Whales
... 31 ...... 31 ..... 31 / 30 ......... 08 / 31 .... R.I.P. Phantoms
Last edited Aug 6, 2008 20:24:55
