Dividing the Big Ten … with Excel

I was inspired by this post on Black Hawk Gold Pants about dividing the Big Ten to take up the question myself.*

*As you probably know, Nebraska is now part of the Big Ten, making it a 12-team league and necessitating (probably) a two-division set-up.  If you didn’t know this, what are you, living under a rock?

BHGP discusses the split mainly in terms of geography and preserving rivalries, but I would like to approach it from a different angle: maintaining competitive balance.  Ideally, we would want both divisions to be equally strong so that we could avoid a Big 12 North/South-like disparity in divisions.  How do we measure strength?  There are many ways you could go with this, but for my quick take, I used conference records.  For the Big 10, Big 10 conference record; for the new team, Nebraska, Big 12 record.  It’s not perfect, I know — Nebraska played an entirely different set of teams, and some teams in the Big Ten played much easier schedules than others in certain years — but for a back of the napkin take on things, it’ll have to do.  I may go back later and re-do this analysis with some more sophisticated rating system, like the Sagarin ratings, but conference records are a decent proxy for quality, I think, and will serve.  To get a general picture of program strength, I averaged conference record for the past 10 years (2000-2009).  Here’s how the teams ranked in conference record winning percentage over that span:

1. Ohio St. (.800)

2. Michigan (.663)

3. Iowa (.613)

4. Nebraska (.579)

5. Penn St. (.563)

6. Wisconsin (.550)

7. Purdue (.513)

8. Northwestern (.475)

9. Michigan St. (.400)

10. Minnesota (.375)

11. Illinois (.325)

12. Indiana (.225)

On first glance, a couple things stand out: first, go Iowa!  The Hawkeyes may not have the reputation as the class of the Big Ten, but over the past ten years, they really have been right there.  The gap between them and Penn St. is surprisingly large given Penn St.’s reputation as a dominant football program.  Second, Ohio State is really the gorilla in the room.  Winning 80% of your games on average over ten years is pretty outstanding, and puts them far beyond the rest of the league (the gap between #1 and #2 is roughly the same as the gap between #2 and #7).  Balancing the divisions in a mathematical sense will have to take Ohio State’s dominance into consideration.  The third thing that jumped out at me was how good Northwestern was, at least compared to my mental image of Northwestern.  Once upon a time, Northwestern was in the position that Indiana occupies now — perpetual doormat — but no longer.  They are solidly middle class in the Big 10, and that is important to keep in mind.  Old biases die hard, and so the initial reaction of many to seeing, say, Indiana and Northwestern in the same division might be, “how come one division has both doormats?”  Northwestern is not a doormat, though, or at least it hasn’t been over the past ten years.  Illinois and Minnesota have been much worse over that span, and deserve the “doormat” label much more than Northwestern.

With that in mind, my plan for balancing the division was as follows: pick teams such that the each division’s overall record was the same.  You could probably do this through trial and error, but I decided I’d try out Excel’s solver and see if it could give me an answer.  If you’re not familiar with Excel’s solver, it basically involves a) setting up some number you want Excel to solve for, b) setting up a function that produces that number (e.g. y = x + z), c) and then setting up any particular constraints on the answers you will find acceptable (e.g., you could say, “make sure x is less than 10″ or “make sure z is an integer”).  If you’re interested in the details, there’s a technical postscript at the end of this post.  If not, the basic idea is that I told Excel, “split up the teams into two divisions with roughly the same records.”  And here’s the answer Excel gave me:

Division 1 (aka Division Chuck Norris)

Michigan St.

Illinois

Ohio St.

Minnesota

Penn St.

Nebraska

Average Winning Percentage: .507

Division 2 (aka Division Bruce Lee)

Iowa

Indiana

Northwestern

Michigan

Purdue

Wisconsin

Average Winning Percentage: .506

Geography and rivalries were not taken into account here.  Nebraska and Penn St. share a division, even though they are 1000 miles apart, and Michigan and Ohio St. aren’t in the same division, which would be a non-starter for most fans.  The only thing I can really say for this set up is that it is remarkably well-balanced.  Over the past ten years, the Norris division had a winning percentage of .507, while the Lee division had a winning percentage of .506.  Just eye-balling it, though, I see some problems.  The Norris division is too top-heavy, with bruisers Ohio St., Nebraska and Penn St. sharing space with weak Illinois and Minnesota programs and a mediocre Michigan St. program.  The Lee division is more balanced, but a little blah, with one doormat (Indiana) and five solid programs.

So this initial solution is a little lacking.  Still, I think the goal of balance is a worthy one, and we can use these numbers to judge the balance of various human-based proposals.  I’ll take two examples that I saw on the BHGP page.  The first comes from a site called Shirts With Random Triangles and divides the league as follows:

East

Michigan

Michigan St.

Ohio St.

Penn St.

Nebraska

Indiana

Average Winning Percentage: .538

West

Wisconsin

Minnesota

Iowa

Purdue

Illinois

Northwestern

Average Winning Percentage: .475

This seems pretty far out of whack to me, both based on the numbers and just looking at the teams.  The East is the clearly better division, with four heavyweights, one doormat and one mediocre team.  The West, meanwhile, has only one “heavyweight” in Iowa (and most people wouldn’t call Iowa a heavyweight), two doormats, and three good-but not-great teams.  As far as the math goes, 6.3% may not seem like a lot, but spread over an entire division, it is, and we can do better.

SWRT gives a second scenario, based on a north-south split, which, in fact, does do better:

North

Wisconsin

Michigan

Michigan St.

Minnesota

Iowa

Northwestern

Average Winning Percentage: .513

South

Ohio St.

Indiana

Illinois

Purdue

Penn St.

Nebraska

Average Winning Percentage: .500

This is closer to balanced and is geographically coherent, but still presents the problem of breaking up the Michigan-Ohio State rivalry and a long trip between Lincoln and Happy Valley.

The third example comes from Adam Jacobi of BHGP:

East

Michigan

Ohio St.

Penn St.

Northwestern

Indiana

Illinois

average winning percentage: .508

West

Michigan St.

Iowa

Nebraska

Purdue

Wisconsin

Minnesota

average winning percentage: .505

This is much better.  The balance is pretty darn close, plus it maintains the Michigan-Ohio State and Iowa-Wisconsin rivalries, sets up an intriguing Iowa-Nebraska rivalry, and is reasonably coherent geographically.  The East is a bit top heavy, but this is inevitable with any division that contains Ohio State — OSU is such an outlier that it needs to be balanced in the other direction.  I’m sure you could fiddle with a few things if you wanted to, but this seems like a promising approach.

Technical note

Here’s the set-up I used in Excel:

The first column, labeled “10year”, represents the teams’ 10-year winning percentage.  The second column, “East1West0″, represents the division the team has been assigned to, with 1=East and 0=West.  The third column, “EastSum”, is simply the first column multiplied by the second column, and is used to total up the winning percentages of the East division.  In the solver, I told Excel to solve for the sum of the East Winning Percentages = 3.041, which is exactly half of the total winning percentages of all 12 teams when added together.  I gave the following constraints:

1) East1West0 must be an integer

2) East1West0 must be less than 1 and more than 0 (i.e., must equal 1 or 0)

3) The column East1West0 must sum to 6 (i.e., there must be 6 teams in the East and thus 6 teams in the West).  In the advanced settings, I told Excel to use a precision of .001 and a tolerance of 5%.  I had to lower the precision from its default value of .0000001 or whatever so that Excel could find a solution (this explains why the winning percentages in my two imaginary divisions are roughly, but not exactly, equal).