I love it.
yes, really enjoy it.
Ok, thanks. I'll plan to keep it going then.
If anyone wants to see "the sausage", here are the nuts and bolts behind it:
I have a giant spreadsheet which starts with the 14 teams. With 14 teams, each has 13 opponents so if we played a full double-round-robin that would be 26 games. We don't, we play 20 so each team has six "games not scheduled". After team name the next six columns are for those six games not scheduled. The first three are labeled "noAway" and are for the teams that are not visited and the three after that are labeled "noHome" and are for the teams not hosted. In the case of Purdue top team below) they did not visit Illinois, Iowa, and Rutgers. They did not host Michigan, Northwestern, and Wisconsin.
Then, once we agree on initial tiers I add in each teams' hypothetical projected record in a full, 26-game double-round robin. In Purdue's case this was 23-3 with losses in Bloomington, Piscataway, and East Lansing. This is entered in "RR-W" and "RR-L".
The next two columns are labeled "miss-W" and "miss-L". Here I look at the games not scheduled and determine how many would have been projected wins and how many would have been projected losses. In Purdue's case this year, they missed five scheduled wins and only one scheduled loss.
The next two columns are labeled "miss-W" and "miss-L". I had to add these two when COVID hit to account for scheduled games not played. As you can see, this year MSU missed a scheduled projected win (vsMN) and Minnesota missed a scheduled projected loss (atMSU) and the other 139 scheduled B1G games were played.
The next two columns are "Proj-W" and "Proj-L". Those are projected wins and projected losses. These columns are formulaic as they simply subtract the missed games from the hypothetical projected double-round-robin record to get a projection for the games actually played or to be played.
The next three columns compare the projected record on a 26-game double-round-robin to the projected record for games actually played or to be played. As you can see, Nebraska, Illinois, and Iowa had the most beneficial schedules while Indiana had the most detrimental schedule.
The next three columns, "+Var", "-Var", and "Var" are for upsets. Each positive upset is +1 in the "+Var" column. Each negative upset is -1 in the "-Var" column. The "Var" column is formulaic and simply nets the upsets. So for the top few teams this year:
- Purdue was net -3 with one positive and four negative upsets.
- Indiana was net +1 with three positive and two negative upsets.
- Rutgers was net -2 with four positive and six negative upsets.
- Michigan State was net even with two up and two down.
The next two columns "Conf-W" and "Conf-L" are the projected conference record for each team after accounting for everything listed above, ie:
- Initial projected double-round-robin record
- Less the six games not scheduled
- Less any games not played for other reasons
- Adjusted for upsets
- Equals projected conference record.
Then I have two columns for OOC records which I add just so that I can show the projected overall record as well.
Next I have a column that repeats the team name (this is because at this point the spreadsheet is so damn wide that it is hard to follow it all the way across.
After that I have two columns that give me the projected overall record. These are labeled "ProjT-W" and "ProjT-L" and simply add together the projected conference records and the OOC records.
Next is a column labeled "Chk". This is simply a check figure column to make sure I have everything entered right.
Everything to the right of that is for projected then actual results. I have the entire B1G schedule entered into my spreadsheet. When I set it up preseason I use lowercase "w" and "l" to denote projected wins and losses and when the games actually get played I switch to uppercase "W" and "L" to denote actual wins and loses. When an actual result deviates from the projection that result gets added to the various "Var" columns for upsets.
The "ProjC-W" and "ProjC-L" columns have nothing to do with the columns to the left. They are formulas that come from the w's and l's on the schedule. Then the "Chk" column compares the calculated W's and L's to the scheduled W's and L's. If they match (right now they all do) that column is like it should be. If they don't, I need to fix something.
So, when we get into next year's BB thread, as soon as you see that the B1G has released the schedule, please let me know.
