Menu

How do I get random numbers from a certain range?

Search this Thread

#1

Report Post

Posted to microsoft.public.excel.worksheet.functions

Joe User[_2_] Joe User[_2_] is offline

Joe User[_2_] is offline

external usenet poster

Posts: 905

Default

How do I get random numbers from a certain range?

"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.

Reply With Quote

Reply With Quote

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

Posting Rules
Smilies are On [IMG] code is On HTML code is Off Trackbacks are On Pingbacks are On Refbacks are On

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Unique RANDOM NUMBERS within specified range	Matt D Francis	Excel Worksheet Functions	5	July 15th 08 08:04 PM
How to generate random numbers from a range of cells?	fruitchunk	Excel Worksheet Functions	3	December 7th 07 04:49 AM
how do you shuffle a range of numbers at random	Ann	Excel Worksheet Functions	1	August 30th 07 04:34 PM
How to select top six numbers from a of range of random numbers	Jack M Taylor	Excel Worksheet Functions	4	January 30th 07 09:18 PM
Non-random numbers generated by excel's data analysis random gener	Harlan Grove	Excel Discussion (Misc queries)	2	September 13th 05 04:06 PM

All times are GMT +1. The time now is 05:21 PM.

About Us

"It's about Microsoft Excel"

Contact Us ExcelBanter forum home Privacy Statement