0

I have a list of names, where each name is in cells D2 through O2. I want to select three names randomly.

How can I do that?

Here is my best attempt so far:

=ArrayFormula( UNIQUE(QUERY( SORT( D2:O2 , RANDBETWEEN( SIGN( ROW( D2:O2 ) ) , 1E+293 ) , 1 ) , "select * limit 20" ) ))

When I do this, I get an odd error:

Array result was not expanded because it would overwrite data in O110

where the formula was input to cell O110.

  • 1
    I made this work by moving my range to a column. Not sure why that worked... – kilojoules May 15 '17 at 21:46
2

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.