How do I get random numbers from a certain range?

  • Thread starter Thread starter Random numbers
  • Start date Start date
R

Random numbers

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?
 
Assuming your numbers are in A2:A469...

In B2:B469, enter =RAND() and then sort A and B based on B, and take the top
64 (A2:A65)

OR: you can use formulas to pull a different set of 64 each time you
re-calc, which may not be what you want.

HTH,
Bernie
MS Excel MVP
 
Put your values in B1 thru B468.

In A1 enter:
=RAND() and copy down

In C1 thru C64 enter:

=VLOOKUP(LARGE(A$1:A$468,ROW()),$A$1:$B$468,2,FALSE)
 
Set up a table in Dand E columns with the D column from 1 to 468
and E column is your list of unique numbers
in B column you enter a vlookup formula and extend it to 64 rows
=VLOOKUP(A1,$D$1:$E$468,2,FALSE)

Assign the following macro to a button

Sub TheBestLuckyLottoPicker()
Dim t As Integer, m As Integer
k = 64: n = 468
Do While m < k
Randomize
If (n - t) * Rnd() < k - m Then
m = m + 1
Cells(m, 1) = t + 1
End If
t = t + 1
Loop
End Sub

This macro will produce 64 random numbers from 1 to 468
they will be sorted in the A column
the lookup formula will give you your unique numbers.

Greetings from New Zealand
 
Random numbers said:
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?
Click to expand...

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.
 
Back
Top