Blog Post

Excel Blog
3 MIN READ

How We Built the College Basketball Prediction Spreadsheet in Excel

DavidM's avatar
DavidM
Icon for Microsoft rankMicrosoft
Mar 18, 2019

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.

 

User input: weight for each of the prediction criteria

 

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.

 

Output: Tournament Bracket 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

Updated Mar 18, 2019
Version 1.0
  • RickRank490's avatar
    RickRank490
    Copper Contributor

    Agree with previous poster. Would like to see the current version.

  • NateMcTernan's avatar
    NateMcTernan
    Copper Contributor

    I've been looking over this excel doc for the last couple days and think this is really cool. I see that it was from 2019 and that its all the teams data from then, is there anyway to update it to the 2022 season and stats. Beginning to realize how difficult it is to manually input all the new data and it seems like a lot of the charts were imported from other sites (maybe NCAA.com).