Celect Random Numbers from a Column
November 12, 2001 - by Bill Jelen
Someone (why the rash of people not giving their name?) wrote:
I have a column of 50 numbers and want to randomly select 5 of them. Can this be done?
There are several approaches to this. Here is one way to accomplish this. Let's say that your 50 numbers are in cells A2:A51.
- Highlight cells B2:B51
- Enter the formula
=RAND()and hit Ctrl + Enter. This will enter the formula in all 50 cells at once
- Copy B2:B51 with Ctrl + C
- PasteSpecial the formulas into values by using Edit - PasteSpecial - Values - OK
You have now assigned a random number to each row. You could sort by column B and take the top 5, the bottom 5, whichever you prefer. If for some reason you can not sort the data, you could use column C and enter a Rank function.
- In cell C2, enter this formula:
- Copy the formula from C2 to C2:C51
Anything with a value of TRUE is one of the 5 randomly selected numbers.