Google-sheets – Choose randomly from a list in Google Spreadsheets

google sheets

Oftentimes you may have a "giveaway" of sorts. You have 20 people that put in their names into a drawing, and you want to choose say 3 of them at random.

Is there an easy way to do this via Google Sheets?

I know I can create a =RANDBETWEEN(1,20) several times, but that has two problems: every time I make a change the formula is recalculated, choosing a new number; additionally, if I have more than 10 winners, it's very likely that several of those RANDBETWEENs will conflict with each other. E.g. saying that it should be awarded to the same person multiple times.

Once a name is chosen, it should be removed from the list so that the random function can't choose it again.

I see there's a very complex formula I could use, but that will probably run into the same problems that it'll change each time the sheet is modified. I see there's also an extension I could install, but I'd rather avoid 3rd party extensions.

Best Answer

Copy the range to another location. Highlight the range, right-click it, and choose Randomize range. You now have a new sorting for the range.

You could easily use this new sorting to choose the top N winners. E.g. the first 3 names in the list are your randomly chosen winners.

Screenshot

enter image description here

Note

As mentioned in my comment, the CHOOSE formula is present in Excel and Google Sheets. You need to use a semicolon in stead of a comma.

Example

I've created an example file for you: CHOOSE

Reference

Google-sheets – Randomly select three names from a list

Here is a solution along the lines of what you attempted, but simpler. First, a version for the case when the names are in a column, such as A2:A20 (which I think is more typical):

=query({A2:A20, arrayformula(randbetween(0, 1e20+row(A2:A20)))}, "select Col1 order by Col2 limit 3", 0)

Explanation:

arrayformula(randbetween(0, 1e20 + row(A2:A20))) creates a matching array of random numbers. One has to use randbetween and not rand, because it's necessary to pass an array argument, and rand does not accept arguments. The number 1e20 is "something that's large enough": we want as much entropy as possible in the random numbers, which is 64 bit in the double-precision format used by Sheets. 1e20 covers that easily.
{A2:A20, ...} create a two-column array
select Col1 order by Col2 limit 3 picks the three entries with smallest random numbers.

Data in a row: this requires a small adjustment in the construction of the array: it's made of two rows and then transposed for querying. If the data is in A2:O2, it would be

=query(transpose({A2:O2; arrayformula(randbetween(0, 1e20+column(A2:O2)))}), "select Col1 order by Col2 limit 3", 0)

Best Answer

Related Solutions

Google Sheets vs Excel – Differences in CHOOSE Function

Screenshot

Note

Example

Reference

Google-sheets – Randomly select three names from a list

Related Topic