ExcelBanter - View Single Post - How do I get random numbers from a certain range?

Joe User[_2_]

"Random numbers" <Random wrote:
I have 468 unique numbers and need to select, at random, 64 numbers that
do not repeat. How do I get Excel to do this?

Suppose your numbers are in X1:X468. In some other column, e.g. Y1:Y468
(although it does not need to be parallel), enter =RAND(), starting with Y1
and copying down. Then in A1:A64, put the following formula, starting with
A1 and copying down:

=INDEX($X$1:$X$468, RANK(Y1,$Y$1:$Y$468))

Pay close attention to what's a relative or absolute reference.

Note: Since RAND is a volatile function, you will find that A1:A64 changes
every time any cell is modified manually on any worksheet in the workbook
:-(. Probably not what you want. To avoid that,
copy-and-paste-special-values Y1:Y468 back onto itself. If/when you want to
generate another set of numbers, you would have to fill Y1:Y468 with =RAND()
again. Alternatively, use myRand below instead RAND:

Function myRand(Optional arg) As Double
Static first As Integer
If first = 0 Then Randomize: first = 1
myRand = Rnd()
End Function

If you use myRand exactly as you use RAND -- i.e. no argument -- you can
generate another set of numbers by pressing ctrl+alt+F9.

Alternatively, if you use myRand($B$1), for example, you can generate
another set of numbers simply by editing B1, for example by pressing F2,
then Enter.