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
isn't listed in your Tools menu you'll need to add
the Analysis ToolPak using the Tools Add-ins...
menu command.
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
'Written by OzGrid Business Applications
'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
OzGrid Business Applications
