1. Let's say I have a column of 20 numbers, each one in a separate cell.

How do I choose X (say 6) random numbers from such a set? (in Excel 2007).

I poked around in the function list but nothing seems to do what I want.

2. If you range is A1:A20 you can put the formula in a cell:

=INDEX(\$A\$1:\$A\$20,20*RAND()+1)

And copy it 6 times and you will get 6 random elements from the list.

Steve

3. Originally Posted by SteveA
If you range is A1:A20 you can put the formula in a cell:

=INDEX(\$A\$1:\$A\$20,20*RAND()+1)

And copy it 6 times and you will get 6 random elements from the list.

Steve
Hi Steve,

The problem with a formula-based approach is that the same sumber could be repeated two or more times.

http://bro.ws/776957L

5. Originally Posted by macropod
Thanks for that pointer Macro. The tip you pointed to was complex and did not do exactly what I wanted. So I did a search there and came up with this, which does exactly what I want!

http://www.excelforum.com/excel-gene...generator.html

6. The problem with a formula-based approach is that the same number could be repeated two or more times.
But that is the nature of what you asked for: "How do I choose X (say 6) random numbers from such a set?"

Pulling 6 out of 20 possible items, if they are truly random may yield some repeats. If you want 6 unique items then you do not want a random sample...

If you want 6 items with no repetitions: you can have your list of 20 items in B1:B20
The in In A1, enter:
=RAND()
Fill down by double clicking the fill grip in the lower right corner of A1 to put the formula in A1:A20

Now if you want 6 random items without replacement, in D16, enter in D1:
=VLOOKUP(LARGE(\$A\$1:\$A\$20,ROW()),\$A\$1:\$B\$20,2,FALS E)

Copy D1 to D26

Now <F9> will constantly give you a new sample. Column A could be hidden if desired. Or if you wanted to put it onto another sheet, instead of Vlookup use, match and index combination.

Steve

7. Steve - I was negligent in not specifying that I did not want repeats.

Your solution appears to be somewhat similar to the link to the solution in Excel tips that I posted above.

There is always more than one way to skin that old cat!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•