This post contains version 2.0 of the playoff Excel sheet (at the bottom). If something does not work, please leave a comment on the post.
I teach at a high school where our football team may need to travel great distances dependent on its playoff qualification. Bus trips of 300-400 miles are not uncommon at our school given our location at the western end of the state of Nebraska. This poses many issues: lost instructional time, increased transportation cost, and increased numbers of substitute teachers in our building. Projecting scenarios becomes really important to our coaching staff. The past two years, I have been able to make these predictions accurately using Excel.
Here is a copy of this season's playoff projection sheet for Nebraska Class B football. Follow these instructions and guidelines. The link to the Excel sheet appears at the bottom of this post.
When you open the file, you must enable macros (enable content).
We want to know what effect particular win/loss scenarios have on playoff seeding. See the table to the left. To make changes, either type a capital W or a capital L into the red "Results" cell. Then press Enter. The table will automatically assign the opposite value to the blue column.
Immediately after changing the table, look at the bottom of the Excel spreadsheet. Click on the tab marked "Projection."
After clicking on the "Projection" tab, press CTRL+SHIFT+Q (This does not mean press the + key... it means you need to press the three buttons all at once: CTRL and Shift and Q). Excel may or may not take you back to the original sheet. If you do not automatically see the original sheet, then click on the "COACHES TOY WITH THIS" tab.
Next, select cells N3:O35. Then click on the "Sort & Filter" button.
Click on the leftmost dropdown box labeled "Column". Select "AvgPP_9games".
Next, make sure the "Sort On" dropdown box says "Values."
Last, change the "Order" dropdown box to say "Largest to Smallest."
Then, click OK.
The last step is to press CTRL + SHIFT + Q one last time. The green table should reflect the 16 playoff teams. Pressing the three key shortcut is necessary because the green table runs a bottom-up sort on the Power Point seeding table to incorporate the yellow district champ table information.
Please note the sheet is not set to break ties. In the example below, let's take a look at Ralston and McCook. They tie for a Power Point total. We need to break this tie. The first tiebreaker, according to the NSAA 2013 Football Manual, pages 17-18, the first tiebreaker is head-to-head play. McCook and Ralston do not play each other during the regular season. Next, we must look at the number of wins each of these teams has against Tier 1 (highest quality) opponents.
We must be cautious about this because the Week 9 outcomes may change the result tables above, taken from the NSAA Complete Class B Schedule page. According to our scenario, AFTER week 9, McCook has a Tier 1 win against Scottsbluff. Ralston has a Tier 1 win over Crete (projected week 9 win from sheet, putting them at 6-3 on year). None of the other teams Ralston has played with 5 wins make it to 6 wins based on the spreadsheet example we are using. We next go to the greatest number of wins over Tier 1 and Tier 2 teams. This gives McCook 5 wins over Tier 1/Tier 2 teams after week 9 and gives Ralston 5 wins over Tier 1/Tier 2 teams.
The last element of the tiebreaker (before coin flip) is to look at the total win/loss percentage for a team's combined opponents. This can be simplified to counting the total number of wins a team's opponents have after Week 9. For example, we would need to count the number of wins by Aurora, Scottsbluff, Northwest, Gering, Sidney, Alliance, Hastings, Holdrege, and Lexington to determine McCook's count (38 based on our week 9 scenario in the sheet), then repeat the procedure for Ralston's opponents (41). So this breaks the tie, and Ralston is awarded the higher seed. The sheet ranked Ralston higher coincidentally; the user would need to verify and break ties in Power Point totals.
The user must also be mindful of the winning percentage of the teams listed in the orange box. Four of these teams are Class C1 in Nebraska. Vermillion, SD is the only out of state team in the Class B schedule.
For convenience, I have set each of these cells to include a drop down box for the four teams in each district.
The link to the projection sheet is below. Before downloading this, please recognize I teach math teacher for a living, and I built this sheet recreationally in my spare time.
Special thanks to Andrew Bartow, one of my students, for his help with Visual Basic and constructing macros. The spreadsheets I have used the past two years have been really time consumptive with If-Then statements. Andrew's work helped streamline this process for years to come.
It's a BIG file. Be patient.
The following errors have been fixed since the original release:
- Incorrect range of cells tabulating wins/losses for Aurora has been corrected.
- Computation of Power Points for teams with 3 wins after week 9 has been updated. Previous version counted these teams as Tier 2 by mistake.