[EMAIL PROTECTED] writes:
>I WOULD LIKE HELP ON FORMULATING DATA ON EXCEL THAT WOULD EXECUTE
>RANDOM NUMBERS WITHOUT REPEATING. I TRIED USING THE FOLLOWING FORMULA
>BUT IT REPEATS SOME OF THE NUMBERS: =RAND()*39. ONCE I ENTER THIS
>FORMULA A NUMBER SHOWS UP ON CELL A1 AND THEN I JUST DRAG TO OTHER
>CELLS AND EXCEL AUTOMATICALLY GENERATES OTHER NUMBERS BUT REPEATS SOME
>OF THEM. I NEED HELP SO THAT IT DOES'NT REPEAT THEM . THANKS. AL.
Glad to help. but for the record, you should probably take the time to use
both upper and lower case in your questions. It makes it easier to read. The
trick here is to list all your possible values in order, then add a second
column of random numbers. Sort the two columns by the random number column,
and then select as many as you need from the top.
Suppose for example, that you want to select 3 digits randomly from the
digits 1-9 without replacement.
Here's the original spreadsheet.
1 0.6701
2 0.1608
3 0.4525
4 0.6574
5 0.7865
6 0.6826
7 0.3397
8 0.2736
9 0.1502
Here's the sorted spreadsheet.
9 0.1502
2 0.1608
8 0.2736
7 0.3397
3 0.4525
4 0.6574
1 0.6701
6 0.6826
5 0.7865
So the three randomly selected digits, without replacement are 9, 2, and 8.
This algorithm is easy to implement and easy to understand. It doesn't work
so well if the problem is very large, though.
I hope this helps.
Steve Simon, [EMAIL PROTECTED], Standard Disclaimer.
STATS: STeve's Attempt to Teach Statistics. http://www.cmh.edu/stats
Watch for a change in servers. On or around June 2001, this page will
move to http://www.childrens-mercy.org/stats
=================================================================
Instructions for joining and leaving this list and remarks about
the problem of INAPPROPRIATE MESSAGES are available at
http://jse.stat.ncsu.edu/
=================================================================
