Random Numbers

Posted by Alisa on April 18, 2001 8:09 AM

I'm trying to get Excel to return a list of random numbers, but using the RAND and RANDBETWEEN functions don't help because a unique # is not returned every time (#'s repeat). Does anyone know how to do this?

Posted by Mark W. on April 18, 2001 8:26 AM

How many random numbers are you trying to produce?
What should the range of these numbers be?

Posted by Alisa on April 18, 2001 8:31 AM

I need 2000 unique #'s between 1 and 2000.

Posted by Mark W. on April 18, 2001 8:36 AM

Please claify. Do you mean to say that you'd
like to randomly order the values 1 thru 2000?

Posted by Alisa on April 18, 2001 8:48 AM

Yes, that's what I want to do.

Posted by Mark W. on April 18, 2001 8:54 AM

For discussion purposes...suppose that A1:A10
contains {1;2;3;4;5;6;7;8;9;10}. You can enter
=RAND() into B1 and copy down to B10. Next,
=INDEX(\$A\$1:\$A\$10,RANK(B1,\$B\$1:\$B\$10)) into C1,
and copy down to C10. This will produce a new,
randomized list in column C.

Posted by Alisa on April 18, 2001 8:56 AM

Yes, that's what I want to do.

Posted by big bob on April 18, 2001 9:04 AM

In column A put the numbers from 1 to 2000
In column B put the formula =RAND() for each
cell with a number in column A.Select a cell
in column B and click one of the sort buttons
on the toolbar .Column A will now contain a
randomly sorted list and each click of a sort
button will generate another different one.
HTH Bob

Posted by Alisa on April 18, 2001 9:06 AM

Thanks,it worked!

Posted by Alisa on April 18, 2001 9:47 AM

I am able to generate the list of #'s, but every time I add text to any other cells, save changes, etc., the #'s change. Is there any way I can prevent this?

Posted by Mark W. on April 18, 2001 10:05 AM

Alisa, there are couple of ways that you can preserve
the random numbers.

1. Use Copy/Paste Special Values to overwrite the
values produced by the RAND() function. If, you
need to produce a new set of random numbers later
you'd just re-enter the RAND() function.

2. Use the Random Number Generation facility of
the Tools Data Analysis... command. If this command
the Analysis ToolPak using the Tools Add-ins...

Posted by Mark W. on April 18, 2001 10:18 AM

Oh, yeah...there's a 3rd option...

Put your random list is a separate Workbook. Create
a defined name (i.e., randList) that refers to the
list range (\$C\$1:\$C\$10, in my example). Reference
this new defined name from any of your other workbooks.

Posted by Mark W. on April 18, 2001 10:20 AM

Re: Oh, yeah...there's a 3rd option...

Don't forget to close the workbook containing the new
defined name randList.

Posted by Dave Hawley on April 18, 2001 9:36 PM

I created a macro to do this for me some time ago.

Sub RandomNumberGenerator()
'Creates a list of random numbers _
between 1 and 36 in range A1:F6
'www.ozgrid.com
Dim Rw As Integer, Col As Integer
'Clear the range ready for random numbers
Range("A1:F6").Clear
Randomize ' Initialize random-number generator.
For Col = 1 To 6 'Set the Column numbers
For Rw = 1 To 6 'Set the Row numbers
'
Cells(Rw, Col) = Int((36 * Rnd) + 1)
Do Until WorksheetFunction.CountIf _
(Range("A1:F6"), Cells(Rw, Col)) = 1
Cells(Rw, Col) = Int((36 * Rnd) + 1)
Loop

Next Rw
Next Col
End Sub

Feel free to use it.

Dave