Building the Excel basketball bracket

The NCAA March Madness bracket and Basketball Pool Manager file in Excel were very popular this year (over 10000 downloads, which isn't bad considering I haven't attempted any publicizing or SEO), and Nick at Excel Spreadsheets Help asked me to write a guest post about the brackets. I've reprinted the post below with some additional babbling, or you click here to read the original.

-----------------------------------

The process of administering an NCAA basketball pool can be tedious and fraught with human error. To alleviate this, we can use a two-file Excel solution: a Bracket file submitted by all pool participants, and a Pool Manager file used to track the results once the tournament has begun.

Below is a basic overview, but I recommend poking around the files to learn more. Please bear in mind that the bracket files aren't perfect, and there are better ways that I've often been too lazy to implement. The building of these was a long, very incremental process. I built the first bracket in 2002 and usually only worked on the files for a day or two each March, implementing minor changes each year. It's certainly not the best way to build a program, and it's far too typical of how products are developed. Still, the current files serve their purpose and I hope they can be of interest.

The Brackets

Most importantly: we need good, clean data. Let's say there is a game involving Massachusetts. If the participant puts the winner as "UMass," a human with context knowledge understands what that means, but Excel doesn't. The file needs to have "Massachusetts" exactly.

To keep data consistent, all participants must submit the exact same bracket file. By using VBA code that allows the user to click on a cell to advance that team, there is no opportunity for mis-typing. The user clicks on "Massachusetts" in cell B4 and it automatically advances the cell's contents ("Massachusetts") to cell C5. The user sees this as a quick and easy way to fill out a bracket; in reality, its main purpose was to help ensure good data.

For users who don't/can't enable macros, the file also uses a combination of Data Validation, formulas, and Conditional Formatting to ensure clean data. Having alternative checks is an important component to maintaining clean data. Many thanks to my friend Tom Szarek for the clever design of these great VBA-free features.

Collecting the brackets

With everyone using the same bracket file, we always know what data is going to be in which cells - e.g., C5 will always have the winner of the upper-left region's 1-16 matchup. This allows the Pool Manager file's VBA code to open a participant's bracket (previously saved off to the pool administrator's hard drive), store the picks in an array, and then write those picks at a row of data in the Pool Manager file. This is automatically repeated for all participants, storing all pool participant data on the same worksheet. At this point, the Bracket files are no longer of any use.

Evaluating the brackets

Using formulas, we compare the actual winners of the games (from the MasterBracket tab) with the participant's picks. If 'actual winner' on MasterBracket = the 'pick' on the Picks tab, the participant earns the points from that game. For example, if the pool manager designates "Massachusetts" as the winner in cell C5 on the MasterBracket tab, the formulas will check the participant data in column B of the Picks tab for a match. It does the same for the remaining games, the results of which are in Picks!C:C, Picks!D:D, etc.

The participant score is computed instantly with formulas on the Leaders tab. VBA then sorts the leaderboard in descending order so the highest scorer is on top. The resorting could be done with some array formulas, similar to those used on the PartInfo tab, but VBA is easier and less resource intensive.

Enjoy March!

Any time you are doing the same task multiple times, you need to question how Excel and VBA can be used to reduce the workload. With these files, pool administrators can handle a large number of participants with little additional effort. Further, once the Pool Manager file has all data, the administrator can send the file to all pool participants -- this lets participants track scores on their own, generate scenarios, and see other pool participants' picks. The less time spent on administration, the more time there is to enjoy the games and the taunting of friends in the pool.