Calculating St. Pius X Festus rating - a lesson for coach Chris Miller

 Chris Miller, boys coach for a solid St. Pius X Festus team, inquired about how the model works.  I share a few screenshots with him and am providing them below. 

I use a large Excel spreadsheet for the boys and the girls.  Every team is entered into the spreadsheet and I calculate an average rating by summing the game ratings and dividing by the number of games.  I calculate some other things along the way and it is easy to copy the results and dump them into a report.

Each team is listed in the Excel Formulas section under defined names.  I use abbreviations for each school and link that defined name to the average rating for the team.  When St. Pius X beats 

Valmeyer, Illinois by 31, I add the Valmeyer rating and the 31-point differential to give St. Pius X a game rating of 109.  The table below shows how I get the game ratings that enter into the master spreadsheet.  I don't do a table like this for all teams unless I get a squabble going late in the season and there are three or four top teams that I need to re-evaluate.  

You can see that St. Pius X played opponents with an average rating of 100.5 and their rating was 99.8, meaning that they probably had about a 50-50 chance of winning these games.  Their actual record is    7 wins-9 losses and their point differential of 52.8 scored to 53.5 given up (which works out to a predicted season finish of 48% winning percentage or 13 wins and 14 losses).

But I don't get this deep with every team.  I used the spreadsheet screenshot below this table to calculate team ratings.

I took the teams just above and just below St. Pius X to show the calculation table.  I added a line below St. Pius X to illustrate the defined formulae that are entered into the spreadsheet.

          As I collect team scores, I add them to the list and continue for the entire season.   

         About this time of year, I start to focus on the top 20 or 30 teams in each class and catch up on the           lower-rated teams as I have time.

         I don't know if any of that makes sense.  It probably muddies the waters more than it helps, but               that is how Gramps ratings work.