Results 1 to 3 of 3
Thread: Random Generator Excel97

20010406, 15:41 #1
 Join Date
 Jan 2001
 Location
 Las Vegas, Nevada, USA
 Posts
 335
 Thanks
 8
 Thanked 0 Times in 0 Posts
Random Generator Excel97
I would like to randomize the sequence of numbers within a number, so that the function operating on an integer such as 12345 would display all possible combinations of digits comprising that number. (whew!)
For example, the results would look like; 12345, 23451, 34512, 45123, 51234, 13452, 14523, 15234, etc.
I tried a few series with random, but I just don't get it. THX in advance.

20010408, 13:22 #2
 Join Date
 Jan 2001
 Location
 Baltimore, MD, Maryland, USA
 Posts
 254
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Random Generator Excel97
4/15/2001 PLEASE NOTE: I FOUND A SMALL ERROR IN THE WBK I ATTACHED. In order to work correctly, please copy cells K7:O7 down to K50:O50; the references got messed up when I copied them. Also, as I am sure you guessed by now, you have to enter your number into B2:F2 and copy it down for the algortihm to work.
I have attached a file with a "heuristic" solution to your problem. I set Recalculation to Manual. So, to run it, click F9.
In way of explanation:
There are two basic ways (in XL) to generate whole number integer random numbers: 1) RANDBETWEEN specify the lower and upper bounds. The number generated is from a uniform distribution (equal probabilities for each number);
2) construct your own list of possible numbers in col A and the associated probabilities in col B and use the Random Number Generation Function from the Tools/DATA ANALYSIS drop down menu. Specify 1 for # of variables, input a number for the # of random #s, chose DISCRETE for the distribution and use $A$2:$B$702 for input range and specify an output range for the number of random numbers to be generated. To my knowledge, EXCEL does not have a method for doing sampling without replacement (which is what you need). You can build such an application, which I could do for you (but not for nothing, because I it would be too timeconsuming for me), or you can use a variation of the approach I used on the attached WS file. Inspect it and let me know if you have any questions.
This is VERY close to Sampling WO Replacement and it would take me several hours or a couple of days to explain exactly what the difference might be in statistical terms. But I am relatively sure the difference is purely academic.
You can email me at sstollma@juno.com

20010409, 19:29 #3
 Join Date
 Mar 2001
 Location
 Silicon Valley, California, USA
 Posts
 273
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Random Generator Excel97
Are you asking, how to generate an arbitrary number of random numbers, made up of a limited set of digits, or are you looking for every possible permutation of a set of digits? Either way, once you generate every possible permutation, you can have Excel generate a random number, which is used to index a given permutation. For example, in one column have permutations, in the next column have index numbers:
11111 1
11112 2
11113 3
11114 4
11115 5
11121 6
...
55555 3125
Then use
=INDEX(Permutation,MATCH(RANDBETWEEN(1,3125),Index Num,1),0)
to look up one of the permutations randomly (where Permutation is a named range containing each of the permutations, and IndexNum is a named range that contains the index numbers 1 through 3125).