Simulate a Cup Draw using Excel
Microsoft Excel can do some amazing things. I like to attempt little projects testing what Excel can do. I was asked the other day to simulate a cup draw using Excel and this is the result.
This article will give you access to the completed Excel cup draw spreadsheet and outline the steps that were taken to create it.
The Excel cup draw created in this article can be applied to any sport or game where the goal of the competition is to score higher than your opponent. This makes it work with most sports including football, snooker, darts, softball etc.
Randomly Select a Name from a List
For a cup draw to work we need to be able to pick a name of a person or team from a list at random.
A few steps were taken to achieve this. The image below shows the Data sheet of the cup draw spreadsheet where most of these calculations took place.
Firstly the RAND function was used in column A to produce a random number between 0 and 1.
Then the formula below was entered in Column B to find a unique ranking for each number which will be used as an ID for each person or team.
Rather than actually randomly selecting a name from the list, what we are really achieving is randomly associating a different ID and selecting the person based on that.
Now that we have a random ID assigned, we need to pick a name from the list. The VLOOKUP function was used in the required cells on the Cup Draw sheet to extract the person assigned number one, and then the person assigned number 2 and so on.
The VLOOKUP function looks like the below.
It looks for the number in cell A12 on the Data sheet within the list of names and randomly assigned ID’s returning the required name. For this to work the list of number starting from A12 on the Data sheet will need to match the number of people/teams being used (in this example 8).
Put Winners Through to the Next Round
After creating the fixtures for the first round of the cup, we need to establish who the winners of the round are, and put them through to the next round.
The IF function is used to test if the matches from the round had been played yet, and if so who the winners were.
The function below is entered into the appropriate cells on the Data sheet.
=IF('Cup Draw'!B2="","",IF('Cup Draw'!B2>'Cup Draw'!D2,'Cup Draw'!A2,IF('Cup Draw'!B2<'Cup Draw'!D2,'Cup Draw'!E2)))
Set the Spreadsheet to Manual Calculations
As you work on the spreadsheet you will notice that the RAND function calculates every time you work on the spreadsheet.
For the cup draw to happen only once per round you need to switch the calculations to manual. This will turn the formulas off so that we can program it to happen when we want them to.
In Excel 2007 and Later
1. Click the Formulas tab
2. Click the Calculation Options button in the Calculation group
3. Select Manual from the menu
In Excel 2003 or Before
1. Click Tools > Options
2. Click the Calculation tab
3. Select the Manual option
Insert the Command Buttons
The final step now is to insert the buttons that when clicked, will create the fixtures for the next round.
To keep the VBA coding simple there is a button for each round. Ideally one button would be used to generate the next rounds games. However the idea was to try to do as much as possible without the use of VBA.
To insert a command button;
In Excel 2007 and Later
1. Click the Developer tab on the Ribbon
2. Click the Insert button
3. Select the Command Button under Form Controls
4. Click and drag to draw it onto the spreadsheet
In Excel 2003 or Before
1. Click View > Toolbars > Forms to see the Forms toolbar if necessary
2. Click the Command Button
3. Click and drag to draw it onto the spreadsheet
Right click on the buttons and select Edit Text to change the text on the buttons.
Generate the Next Rounds Fixtures
VBA code will be used to run calculations on specified cells only. The cells specified will be those required to calculate the next rounds matches only.
1. Press Alt + F11 to open the Visual Basic Editor
2. Click Insert > Module
3. Enter the code below changing the cell references where appropriate and close the VBE when finished
And that’s it. The spreadsheet can be improved upon by adding some Excel worksheet protection to protect the formulas, and some formatting to improve appearance.
More by this Author
Form controls can be added to an Excel spreadsheet to create interactivity with the user. This article looks at using option button controls to allow a user to choose the data they want to see on a chart. The first...
If you are using pictures in your Excel spreadsheet you may be having some difficulty with sorting the pictures along with the other data in the list. The good news is that you can sort pictures in an Excel list just...
Brock Lesnar is a phenomenon of a man and an athlete. Brock Lesnar weighs a lean 265 lbs and yet possesses extreme speed, agility and athleticism. How a man carrying so much muscle mass can maintain such high levels of...
No comments yet.