This post has been republished via RSS; it originally appeared at: Excel Blog articles.
We love sports in the Excel team (Soccer, Football, all sports), and now is the time to dive into basketball, just in time for the season! We are excited to share with you our Basketball Tournament Prediction tool, complete with a suggested bracket based on your custom weighting of several criteria we use to predict the outcome for all matches. Read on to better understand how the tool was built and create your bracket. You can download the template here.
Note: This tool is offered by Microsoft Corporation, and is not sponsored, endorsed by, or affiliated with the NCAA. The tool is for fun only and is not in any way intended for use in betting or other uses of value. No representation is made to the accuracy of predictions and brackets derived from the tool.
How the model works
1. Team Strength
The first thing the tool does is calculate the strength for each team that will determine which team wins a match between any given pair. To do so, we leverage various established rankings and statistics to predict the winners in all matchups in the tournament. These are:
- Tournament seed
- NCAA Division 1 Rank
- Scoring Offense
- Scoring Defense
- Won-Lost Percentage
In addition to these rankings/statistics, you can choose to add your personalized individual team’s performance, from 0 (worst) to 10 (best). You can base this on anything you want! From the name, to the team’s mascot!
Finally, a very important input is the weight for each of the criteria. You can assign a weight of 0 (don’t use the ranking/criteria) to 10 (highest weighting). Note this is a relative weighting, so more than the absolute number you use, what’s important is how it compares to the other weights.
2. Bracket
With each team’s strength and the tournament seeding, the tool predicts the outcome for each of the games, giving you the output bracket, as well as the projected national champion and top four teams.
Leave a comment with your thoughts on the model, and good luck with your brackets!
Bonus: Spreadsheet Behind-the-Scenes
We tried to keep the model as self-explanatory as possible. Just in case you’re curious, here are some details of how the spreadsheet is built:
- Ranking calculations (Tournament Seed and NCAA Division 1 Ranking): we used the Rank.eq formula applied on the position for each of the teams. This formula returns the rank of a number within a list of numbers, or the size of a position relative to the other values in the list.
- Scoring Offense and Defense, Won-Lost percentage: These three criteria were normalized, so the scores for all teams spread evenly between 0 and 10.
- Final user adjusted strength: we do a weighted average of all criteria based on your weighting. Columns P-AA in the “User Input” tab have these calculations.
- Match winner calculation: to determine which team will win each of the matches, we do a simple comparison of each team’s strength, and the higher one wins. We bring each of the team’s strength into the “Bracket” tab using the powerful (and now faster!) vlookup
- Data validation: you’ll notice that all cells that require user inputs only allow you to enter integers from 0 to 10. We do this using Excel’s data validation
You can download the template here.
To stay connected to Excel and its community, read Excel blog posts, and send us ideas and suggestions for the next version of Excel through UserVoice. You can also follow Excel on Facebook and Twitter.
David Monroy, PM Manager, Excel