Everybody loves a sweepstake – we always run one at work for The Grand National each year as well as The World Cup and The Euros.

If like me you have organised a sweepstake you will know just how annoying it is folding up all of those little bits of paper, especially if you are doing it as blind draw where you either have to seal each team in an envelope or do a second draw to determine each person’s selection.

With the organising of the sweep often falling to me, I decided that it was time to simplify the process and have a bit of fun with some Excel wizardry. So I created an automated random sweepstake picker!

In this short blog, I am sharing my creation! This blog is about how to use my sweepstake kit and a little bit of explanation as to how it works. Maybe if I get a little bit of time I might write about how I made it…. But that is for another day!

 

Free download at the bottom of this page!

EDIT (30/05/2018) – Updated with additional error capturing and minor cosmetic adjustments.

 

The Problem

I love a sweepstake, but hate the folding up of paper! Time to simplify and automate things….

 

How It Works

It is all centered around Excels ability to generate random numbers. Hidden in the background is a list of all available selections (The “Hat”). At the start, the hat contains all of the selections available in column A. Each time the green ‘Pick’ button is clicked a random selection is taken from the hat and allocated to that person. The hat now only contains the remaining selections. This process is repeated until all are gone.

Once all selections have been sold, then orange ‘Generate Selections…’ button can be pressed to randomly allocate a team to each number. This is done by allocating a random number to each team and reordering the list based on those numbers. The list is then copied to column C.

There are warning messages that will pop up asking for confirmation of resetting and to stop you from over allocating.

You will need to enable macros to use this spreadsheet.

It’s all quite clever really!

How To Use

  • Chose your stake amount and prize pot allocation
    • Enter an amount into cell I3.
    • Choose the positions that will receive a prize (up to 4) – enter these into cells H6:H9 – you may want 1st/2nd/3rd/4th or just 1st/2nd or maybe you want to include Golden Goal or something like that?
    • Choose the percentage allocation for each prize. Cells I6:I9
  • Amend selections
    • If doing a ‘Blind’ Sweepstake then leave column A with numbers, but feel free to change to team names if you would rather not do it blind. (you can have up to 40 selections)
    • If you change the names/numbers in column A, you MUST hit the reset button before starting!
    • Change team names in Colum Q to suit your event (currently set for World Cup 2018 – Russia)
  • Sell your selections
    • Click the green ‘Pick’ button as each person pays and type their name in the input box.
    • Enter payment in column D when received.
    • Repeat until all sold.
  • Allocate team names (if doing a Blind Sweepstake)
    • When all selections are sold, click the orange button. This will do its magic and allocate each person with a team.
  • Tell everyone their teams
  • Enjoy the event
  • Pay winnings
    • Use column E to select the winning criteria against each person (dropdown list). Prize amount will show against each person.

ChangeLog

  • 30/05/2018:
    • Changed ‘InputBox’ to a custom ‘UserForm’ for additional functionality
      • Must enter name
      • Option to mark as paid straight away
      • Variable msgbox for if paid or not
    • Cosmetic changes
      • Unpaid selections are now highlighted using conditional formatting
      • Wider column widths to accommodate longer names and larger stake amounts
      • ‘Shrink to fit’ enabled on cells to allow entry of even longer names!
    • Cancel pick – can now cancel (by clicking red cross on userform) the pick without any issues

 

It should be nice and easy to use and adapt to different events, but please leave a comment below if you would like.

The spreadsheet is available to download for free below, but feel free to ? Buy Me A Beer! ? if you wish!

Cheers and Good Luck!

 

     
295 Downloads

? Buy Me A Beer! ??

 

You might also like

Entertaining an almost-2-year-old
Read more
Back to Basics: Programming
Read more
Excel: Finding Week Number Based On a Date Using WEEKNUM & ISOWEEKNUM
Read more
The Quest for the Perfect Host
Read more

0 comments

Leave a reply