Coaching

Updates:
The C1/Out-Of-State Results are as follows:
Auburn, Tier 2, (3-5)
Chadron, Tier 1, (6-2)
Chase County, Tier 2, (3-5)
Wayne, Tier 2, (5-4)
Vermillion SD, Tier 3, (1-7)

You will need to make the orange table in the Excel sheet match the following:C1_Out_State

Many district titles are up for grabs in tonight's Week 9 games. Gretna won their game last night, as expected, to secure the B3 district title. Good luck to all the playoff hopefuls tonight across Nebraska.

This time, the error was not mine.

A user on Huskerland Preps Message Board found an error in the win/loss source data. The sheet I downloaded from NSAA Saturday morning had a typographical error. The incorrect source data indicated Gretna lost to Elkhorn in Week 8. This is untrue. Gretna is undefeated going into Week 9.

While it is frustrating to me the sheet has had errors, the macros will allow future users to simply download the NSAA source sheet and use this master spreadsheet for years to come, provided Class B continues to have 32 teams and the NSAA continues to use the exact same rows, columns, and format for data entry.

Below is a link to the 3rd version of this spreadsheet. This one appears to be 'right.' The new sheet also has improved functionality in that we added a macro to take care of the Power Point sorting function: instead of manually sorting, simply press CTRL + Shift + Q and the sheet will take care of the rest.

The user must still:
- update C1 and out of state data (available after Thursday night on NSAA)  - choose district champions
- break ties in the Power Point averages

Thanks for your understanding and patience. Please notify me of any necessary revisions. Good luck to all the Class B football teams in their week 9 contests.

How_To_Image

Link to video giving instructions on how to use the Class B sheet

2013 Class B Playoff Seed Projection Version 3

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).

EnableMacro

WinLossTable

 

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."

ProjectionImage

 

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.

SortHighlightSort_PicAfter clicking on the "Sort & Filter" button, choose "Custom Sort." A Sort window should appear.

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.

TiePlease 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.

McCookRalston

 

 

 

 

 

 

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.

OrangeBoxThe 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.

 

Also, the user will need to verify which teams YellowBoxwin the eight district championships, as these district titles automatically qualify a team regardless of win/loss record. DropDown

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.

2013 Class B Football Projection Sheet

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.

 

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

2013_Class_B_Football_Playoffs

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.

SB_2013_Schedule

 

 

 

 

 

 

 

 

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.

SB_Comma_Delimited_Screenshot

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.

ClassB2012Example

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.