Ratings Machine

For my first post of the new season, instead of doing something for myself or my Gators, I’m doing something for you. I found some use for my business school spreadsheet-fu in the realm of college football other comparing BCS title game offenses. I have created, for lack of a better term, a ratings machine that you can use for rating teams. You drag and drop teams into five customizable categories, and it will automatically give you stats by rating and by conference. Note that this is for ratings, not rankings, so while this can help you with your top-25 lists, it’s not designed for creating them. You can download it from this link: Ratings Machine

It should be self-explanatory, since there is some guidance on each sheet of what you should and should not do. However if you want instructions, I will now give you a tour of it, how to use it, and what it can offer you.

There are 5 tabs, and the first is called Rating Names:


Here you decide two things: your rating values and the names for them. The values will be used later for determining the average rating of teams in a conference, so you can choose to use a linear scale, a logarithmic scale, or whatever else you choose. The rating names are also set to bland names, but you can make them whatever you want.

***IMPORTANT NOTE: this is the only place you can change the rating names. They will update automatically everywhere else.

Tab 2 is Team Names. All 119 teams are arranged by conference in columns:


Only the BCS conferences are shown here, but all teams really are included. This is where you determine the names of teams. For instance, the University of Pittsburgh is listed as Pitt. If you want the full Pittsburgh, this is where you change it. Also, Southern Cal is listed as USC; if you want South Carolina to be USC, you change it here. ***IMPORTANT NOTE: Do not change team names anywhere else, because it will cause links to break all throughout the spreadsheet and you’ll end up having to redo everything. You also cannot change the order of the columns. Sorry if you want the conferences alphabetized.

Tab 3 is Team Ratings. This is where you actually categorize teams into the 5 ratings.


In Excel you can drag and drop cells by highlighting a cell or range of cells and then using the border around them to move them around. You use this technique to move teams into into rating columns. If you’ve got OpenOffice.org (yup, it works there too), you have to cut and paste cells. There is a table of stats for quick reference while you’re doing your ratings.

***IMPORTANT NOTE: You don’t have to rate every team in every conference for this to work (in fact, you can erase conferences you don’t care about if you want), but to get the most out of it, you should rate every team in at least two conferences.

Here is a screencast on how to use it. It’s a little rough and for some reason Google Video sped it up after I uploaded it so you may need to pause it to read the text boxes, but you should be able to get the idea:

Tab 4 is Stats by Conference. This is where you get a look at how strong conferences are comparatively.

Stats by Conference

In this screenshot, you can see that I have completely rated the BCS conferences and added in a couple important mid-majors*. This is where you can see what I meant above about rating complete conferences. This tab tells you about the strength of conferences and how strong the average team in a conference is. The average rating tells you how strong any given team in a conference is, but I included the number of total accumulated points to account for the difference in conference sizes. This was mostly necessitated by the difficulty in comparing the 8-team Big East with the 12-team ACC, Big 12, and SEC. Independents are included for the sake of completion only; with only three of them left, comparing them as a group doesn’t make much sense.

***IMPORTANT NOTE: You cannot make changes to this sheet. Everything automatically calculates for you, unless you have changed something elsewhere you should not have, in which case this page will be all messed up.

Tab 5 is Stats by Rating. This tells you about the strength of college football, or at least what part you rated, as a whole.

Stats by Rating

You should see that my ratings mostly follow a bell curve, indicating that the major college football landscape is basically random. I find that interesting, since I did not try to get that result. If I included all 119 teams, it would look like a half parabola, with the number of teams in a ranking increasing exponentially as you go from one rating to the next worse ranking (I know, I’ve tried it). That indicates that the NCAA has let too many teams into Division I-A. But you knew that already.

***IMPORTANT NOTE: You cannot make changes to this sheet. Everything automatically calculates for you, unless you have changed something elsewhere you should not have, in which case this page will be all messed up.

So, that’s it. If you get some use out of it or have ideas on how to improve it, leave a comment or shoot me an email. Happy rating!

*Specifically Hawaii, TCU, Boise State, and Notre Dame. More on these ratings to come.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: