Quote of the Day
Fashion is architecture: it is a matter of proportions.
— Coco Chanel. I hold the same view of product design. There is a balance that must be achieved between price, features, and schedule.
Figure 1: Randomly Choosing a Winner. (Source)
My wife is participating in a friendly contest at work that encourages employees to exercise. The employees record how many laps they walk around a set course during a month. At the end of the month, an Excel "drawing" is held to award a prize to one of the exercisers. To encourage more exercising, the likelihood of winning is to be proportional to the number of laps each person walked during the month. I was asked if I could write an Excel worksheet that would perform this task. I thought it was an interesting spreadsheet that was worth sharing here. There is a simple macro in the worksheet that controls when the worksheet re-calculates (otherwise it re-calculates a winner every time you change anything on the worksheet).
To make things simple for the users, the worksheet has the following features:
- It uses a table that allows my wife to add people as new participants arrive with no change in the random chooser.
- No "helper" columns are used, which are confusing to some folks.
- The key calculation uses an array formula that generates a cumulative sum, which is a useful thing to have in your toolbox.
- When you press the count button, it actually performs the randomizing six times. I wanted to make sure I avoided any random number start-up issues.
This worked great, thanks!
Glad you liked it. I like developing small applications.
mathscinotes
If you wanted to make a list of 5 winners and not repeat any of the winners, how would you modify the spreadsheet.
Thanks.
I see the "count" total is effectively the Total Weights, but what is the significance of the Random Selection figure? And the Index?
We want to run a contest that allows potentially thousands of entrants to accumulate points, potentially dozens of points each. And we want those who have the most points to have a proportionally better chance of winning the drawing. Can we use the Random Chooser you've created here to accomplish this selection, with the Random Selection and Index you've established?
Hi Gerry,
It has been a bit of time since I wrote this and I had to remember how it worked. Like all spreadsheets that use array functions, it is not obvious. Here is basically how it works.
a. A vector (i.e. string of numbers) is created with length equal to the total number of counts. Every president (contestant) is given a number of vector entries equal to his count.
b. A random number (called Random Selection) is chosen that randomly selects an entry from the vector.
c.Index tells you the number of the president (contestant) that won (starting from 0).
It sounds like your problem is the same as my wife had in her fitness contest, so it should work.
All the macro does is run the routine six times to avoid any startup issues I have seen in Excel's random number generator.
mark
Hi! Thanks for making this. I only have mac's numbers on my computer instead of excel and when I opened your file, it says "the array formula couldn't be imported and was replaced by the last calculated value". Is there any way I can fix this so I can use this resources too??
This is amazing! Thank you - saved me the time/trouble of developing this myself 🙂
Thanks for sharing, this is amazing!!
Hey,
I am trying to use this, but the index number isn't changing regardless of all the other cells changing. It is stuck on 1. Any thoughts?
This is absolutely fantastic! It performs exactly like you said and as I needed. I have been looking for ways to create this or find on the internet. I do lots of drawings with weighted sales figures and this helps tremendously! Thank you for taking the time to share this free on the internet!
Wow. So easy and did EXACTLY what I needed. Thank you so much!!!
You're welcome. I have been surprised how many people have downloaded this spreadsheet.
mark
Awesome this is exactly what I needed. It worked perfectly, thank you very much.
Thanks buddy - this is great! Exactly what I need 🙂
By the way, how can I add more contestants? let's say total of 50?
Is it easy to change?
Many thanks mate!
Thank you - this is amazing and has saved me hours of time trying to work out how to write it myself!
Hi, is there a way to prevent the names that were drawn from being drawn again in subsequent rounds? I am planning to use this for a lucky draw. Similarly some participants will get more chances than the other, but I want to exclude those people that have been drawn from being drawn up again.
Thank you.
You could just change their weight to zero, once they've been drawn.
Thank you. So awesome.
I have inserted 561 rows with varying weights (count). when I click "Choose" I notice that the value returned in "Index" is always 13 less than the row that the name actually occurs in. How do I fix that?
Thank you! this saved me hours...
Your welcome.
mark
This is so great! I'm using it for a school raffle where kids get a certain number of entries based on how much pledge money they've brought in for a running contest. The previous volunteers would manually write the kids names on tickets and do a physical drawing, this is going to save a ton of time and resources! Many thanks for making this available online.
This is great!
Hi
it still quite amazing for me after 6 years
I thought someone should did it, but not done so perfect.
thank you.
a pity is i have to sum by name first because my data source is like George 100 George 1
Just wanted you to know that you helped a high school in Southern California with a raffle using this. I searched online for some sites that allow organizations to conduct weighted raffles, and while a few do, they all carry a fee, and they all presume that the purchase of tickets has not yet occurred, and of course they want you to use their platform to collect fees, percentages, etc. The modern online model. So this worked very well and simply - thanks very much!
Thanks for the note. I love to hear this sort thing. My approach with this blog is to show how a regular guy solves the problems that come up in daily life using simple math and tools. I keep the blog simple – no advertising, little opinion, just problem-solving.
mark
Excellent tool! Thank you for sharing. We are using to award recognition to folks based on their total number of improvement ideas.
It would be especially neat if there were a version compatible with Google Sheets. Thanks for the great resource!
Thanks Mark! I too have been scouring the internet looking for a way to conduct a drawing for a safety program at my workplace!
Amazing!!! Thank you so much! So simple. It was how I was thinking about designing a document but you did it so nicely. Thank you! A simple replace of data and it worked perfectly!
I love this sheet! Thank you so much. Is there a way to add even more people to be counted - I tried to just "insert row", and it didn't change the "total weights"
Hi Adrienne,
I just went into the sheet and added a row with my name and count. It worked as it should. I want to help! Some questions:
1. Did you enable macros?
Unfortunately, it does use a small macro.
2. Did you give the sheet permission to run?
Most folks have their Excel configured not to run macro sheets without permission.
Keep asking questions. You can send me your sheet and I can take a look at it. I will send you an email with my contact info and you can send me the sheet you are working with.
mark
Thank you! I wanted to have a raffle for my students weighted by points they earned in class and this sheet worked perfectly.
Glad it worked for you. I really need to turn this into an app ...
mark
This is great! I'm using this 9 years later for an Instagram raffle. Thank you sir
Absolutely love this. Huge value and great work.
Glad it worked for you. Thanks for the note!
mark
Hey, just a quick note from the future to say THANK YOU for this! I am running a contest on my website and googled something like 'picking a winner from a spreadsheet', and up popped your article from years ago with exactly what I needed! I appreciate your generosity.
mathscinotes,
I just wanted to say thank you for this.
I used the code for some Dungeons & Dragons (DM utilities).
Really brilliant work. Thank you again!
https://old.reddit.com/r/TheNameThingPlus/comments/olkh7i/the_name_thing_plus_v071620211/
Thank you! I used this for a reading challenge drawing.
I realize this was created a long time ago now, but I want to thank you. I knew Excel could do it, but I sure didn't want to go down the rabbit hole of figuring out how. I've been using an online tool, but this time, the ticket numbers some individuals had were high enough to make pasting the name over and over again difficult. So, again, thank you! This worked well. *I might change the formatting to make it a little flashier for my next live video reveal.
I have to laugh. I wrote the spreadsheet for the folks at my wife's company who were having a fitness competition. I am amazed at the number of people who use it. I am in the process right now of writing up some of my more popular spreadsheets as web apps that folks will be able to use without Excel. Hopefully, that will make the code more accessible.
Thanks for writing. I love to hear from folks. Today most of my writing is dull data science stuff — I used to do all circuits math, but I changed jobs.
mark