ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel
  • »
  • Technology»
  • Computers & Software»
  • Computer Software»
  • Office Software Suites»
  • Microsoft Office

Simulate a Cup Draw using Excel

Updated on February 14, 2011

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.

Cup Draw Data sheet
Cup Draw Data sheet

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.

Vlookup function to pick random names from a list
Vlookup function to pick random names from a list

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

Putting the winners into the next round
Putting the winners into the next round

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.

Buttons to generate the next rounds fixtures
Buttons to generate the next rounds fixtures

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!

Comments

    0 of 8192 characters used
    Post Comment

    No comments yet.

    working

    This website uses cookies

    As a user in the EEA, your approval is needed on a few things. To provide a better website experience, hubpages.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

    For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: "https://hubpages.com/privacy-policy#gdpr"

    Show Details
    Necessary
    HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
    LoginThis is necessary to sign in to the HubPages Service.
    Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
    AkismetThis is used to detect comment spam. (Privacy Policy)
    HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
    HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
    Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
    CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
    Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
    Features
    Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
    Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
    Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
    Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
    Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
    VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
    PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
    Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
    MavenThis supports the Maven widget and search functionality. (Privacy Policy)
    Marketing
    Google AdSenseThis is an ad network. (Privacy Policy)
    Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
    Index ExchangeThis is an ad network. (Privacy Policy)
    SovrnThis is an ad network. (Privacy Policy)
    Facebook AdsThis is an ad network. (Privacy Policy)
    Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
    AppNexusThis is an ad network. (Privacy Policy)
    OpenxThis is an ad network. (Privacy Policy)
    Rubicon ProjectThis is an ad network. (Privacy Policy)
    TripleLiftThis is an ad network. (Privacy Policy)
    Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
    Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
    Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
    Statistics
    Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
    ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
    Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)