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

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

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

## More by this Author

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

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

- 10
Impaction is when a bearded dragon’s digestive tract has become blocked. It is a very real hazard that is brought about when the beardie has either swallowed something that is too large, or something that it...

No comments yet.