Disclaimer: I am not a computer programmer nor do I teach a class on programming. I do have two programming units within what I teach. One unit is on programming the TI-84 graphing calculator. The other is on using ALICE to teach simple programming constructs. Much of this is independent reading, independent learning, and a lot of trial and error.
Much of the technology learning teachers do arises from need. The need to do something drives the technology teachers choose to incorporate or not incorporate into their practice. The particular need about which I am writing came from my experience as a middle school football coach. Scottsbluff lies on the western end of the Nebraska Panhandle. Playoff sites are a big concern to coaches and teachers alike. Having an away game leads to lost instructional time, higher transportation cost, and big draws on the pool of available substitute teachers. Coaches desire to know the short list of potential opponents given the time demand film exchange presents. For many years, the coaching staff would try and break the playoff seeding up into smaller problems and finding certain minimums. For example, if A beats B, then we might play C, D, or E. After seeing their methodology, I knew I could be more efficient using technology, specifically Microsoft Excel. How could I resist a really complex, interconnected system of math problems?
Let me begin by explaining the playoff system within the classification our school falls. Nebraska has the following 11-man football classifications based on school enrollment numbers: A is the largest, followed by B, then C1, C2, D1, and D2. If you want to see the full blown points system, click here. The short version: the playoff seeding system is based on two things. First, the team's winning percentage puts them into one of three tiers. In a nine game season, teams with 6, 7, 8, or 9 wins are considered a Tier 1 team. Teams with 3, 4, or 5 wins are considered a Tier 2 team. Teams with 2, 1, or no wins are considered a Tier 3 team. Second, the opponent's tier at the end of the season determines the quality points ("Power Points") assigned each team for wins and losses.
Win against a Tier 1: 50 points
Win against a Tier 2: 45 points
Win against a Tier 3: 40 points
Lose against a Tier 1: 38 points
Lose against a Tier 2: 33 points
Lose against a Tier 3: 28 points
At the end of the season, the team with the highest "Power Point" average across the nine game season is seeded #1. Of 32 Class B teams, 16 will make the eight game first round of the playoffs. The higher seed will host a playoff game in the first round. This is a big deal in a state that is nearly 500 miles across from east to west, with 400 of those miles west of Lincoln, NE. Single elimination games are played until the final two teams play the championship game in Memorial Stadium at the University of Nebraska - Lincoln. You can probably imagine, with the fan based Nebraska enjoys, how high a privilege playing in Memorial Stadium is to high school football players across Nebraska.
An additional twist lies in the districts across Nebraska. There are 8 four team districts, labeled B1, B2, ..., B8. The district champions of these eight districts will automatically qualify for the playoffs. Roughly speaking, a team could start the season 0-6, win all of its last three games, and qualify for the playoffs. This adds an additional layer of complexity to the problem. Just because a team makes the top 16 Power Point seeds does not guarantee the team makes the playoffs.
Where will our kids be in the first round this year?
You can view the complete Class B football schedule from the Nebraska School Activities Association (NSAA) here. If you want to toy with the data yourself, you can find the comma-delimited .csv file at this link.
The data is already entered into Excel, ripe for the picking. This is the time of year I am called on by our coaching staff to provide an accurate projection for who will play who in certain scenarios. On the final night of the regular season, I can expect to receive text messages from coaches and friends around the state, asking for the playoff seeds and sites. In both the 2011 and 2012 football seasons, I had the projections nearly two hours prior to any major newspaper, the Omaha World Herald and Lincoln Journal Star included.
I will share the methods I have used to make these predictions below. I set out in 2011 to make a spreadsheet the coaches could use to forecast the first round seeds and sites. First, the coaches would want a simple table of the week 9 games to enter wins and losses. Next, the sheet would have to be completely dynamic, since every team affects all other teams on its schedule. Last, it would need to compute the average power points and rank the teams.
Since my programming background is limited, I approached this problem through trial and error. I knew I could write "if-then" statements to compute Power Points for teams on the bubble going into Week 9. Specifically, teams that are on the bubble in Week 9 are those that enter Week 9 with either 5 wins or 3 wins.
5-3 then wins = 6-3 = Tier 1
5-3 then loses = 5-4 = Tier 2
3-5 then wins = 4-5 = Tier 2
3-5 then loses = 3-6 = Tier 3
These bubble teams wreak havoc on the Power Points of every team on their schedule. I wrote "If-Then" statements to model the impact of these bubble teams on their opponents.
Here is an example of such a statement for a Week 9 Bubble Team:
=IF(X3="W", 50-5*(R15-1), 38-5*(R15-1))
As you can imagine, writing these statements for every team, tabulating the win/loss percentage, making the predictive table, and making the seeds is incredibly time consuming. Below you can find a copy of the Excel sheet I made last year to model the playoffs.
Final B Playoffs Example (from 2012 season)
This year, I am trying to work smarter, not harder. I am working with a student to learn a little about Visual Basic and to use Macros to set up an Excel spreadsheet I can use year after year. In theory, I should be able to download the NSAA source spreadsheet, copy and paste the win-loss data into my spreadsheet, and have the spreadsheet do all the heavy lifting by a combination of explicit formulas and string operations in Visual Basic. I will post the sheet in the coming days once it is complete.