Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

Hi @cflyte,

Could you be a little more specific about the formula you're drafting?

  • Do you want a formula that will select a variable # of random names from a long list, or a formula that will select a specific # of random names from a long list?
  • You say "No repeats in selection required." -- Are you implying that repeats in the selection are acceptable? (this nuance is important when designing the formula!)

Here's a quick formula for grabbing random names out of a list -- see if this works for you:

In a new Calc spreadsheet, I added the following names:

---+----------+-------------
   |    A     |    B
---+----------+-------------
 1 | Jim      |
 2 | Jon      |
 3 | Jem      |
 4 | Jen      |
 5 | Jan      |
 6 | Jam      |
 7 | Archibald|

In a separate column (I just chose cell B2), I used the following formula to select a random cell from my range

=INDIRECT("A"&RANDBETWEEN(1,COUNTA(A1:A7)))

I can get a list of random names by copying this formula into several cells B2 through B10 (etc...). Note that this list may have repeats.

You'll want to tweak the arguments to INDIRECT() and RANDBETWEEN() so that they accurately describe the bounds of your range. Otherwise you may get spurious results in your output stream (e.g. selecting a larger range than your data resulting in selection of empty cells, or selecting a smaller range and never picking certain names)