Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    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.

  2. #2
    3 Star Lounger
    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 Re-calculation 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 time-consuming 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 e-mail me at sstollma@juno.com
    Attached Files Attached Files

  3. #3
    3 Star Lounger
    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).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •