In this video, we look at how to simulate a drawing and pick winners in a contest with Excel, using the RAND and RANK functions together.
Have you ever had to select the winners in a contest? It's easy when you draw names out of a hat, but how would you do it in Excel?
In this video, we'll show you a simple way to do it using the RAND function.
Here's a list of names that represent entries in a contest. Suppose we want to pick five winners.
In a real drawing, we'd pick 5 random names out of a hat. But how to do that in Excel?
Well, when you use a hat, you can think of the winners as having the lowest numbers in the contest. The first name you draw has a number of 1, the second name has a number of 2, and so on. And if you eventually picked all 100 names out of the hat, each person would have a number.
In Excel, we can simulate this idea using the RAND function. The RAND function generates random numbers between 0 and 1.
For example, if I enter RAND in a cell, we'll see a decimal number.
One thing to know about RAND is that it never stops calculating. Each time we change anything on the worksheet, RAND generates a new number. To stop that from happening, I need to replace the function with the value. Usually, the simplest way to do this is is to use paste special, and values, to overwrite function.
Now we have a fixed value in the cell.
To follow this process for everyone in the contest, we need to generate a random number for each person.
To speed things up, I'll first convert the list names to an Excel table. Now all formulas will be copied down the table automatically, and we'll get structured references as a bonus.
At this point, 5 people already have winning numbers, but we need to lock the values to keep them from changing.
Now you might be looking at list of large decimal numbers and wondering how you're going to choose the lowest values?
Well, we could just sort the list. But conditional formatting is more fun and keeps the values in the same order, so let's use that.
We can use a variant of Bottom 10 items.
Now the 5 winners are clearly marked.
What if you want to see the actual ranking, so that you can assign first prize, second prize, third prize, and so on? For that, you can use the RANK function.
To use rank, you need to supply the number being ranked, a reference to the full set of numbers, and the number 1, to indicate that you're ranking numbers in ascending order.
Now each person in the list has a rank, just like they would if we had drawn all 100 names out of a hat.
The Excel RANK function returns the rank of a numeric value when compared to a list of other numeric values. RANK can rank values from largest to smallest (i.e. top sales) as well as smallest to largest (i.e. fastest time) values, using an...