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

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.

=RANK(A2,\$A\$2:\$A\$9)+COUNTIF(B\$1:B1,B1)-1

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.

=VLOOKUP(Data!\$A12,Data!\$B\$2:\$C\$9,2,FALSE)

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

Scroll to Continue

## 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

Sub Calc_quarters()

Worksheets("Data").Range("A2:B9").Calculate

Worksheets("Cup Draw").Range("A2:A5").Calculate

Worksheets("Cup Draw").Range("E2:E5").Calculate

End Sub

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.

Have fun!