random numbers
Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: random numbers

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I need to create a list of random numbers, that do not repeat each other. The range of numbers i will need will be 1 to n. n = any number>1. how can i create a macro to do this.

  2. #2
    Board Regular eliW's Avatar
    Join Date
    Mar 2002
    Posts
    1,919
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-17 06:35, abz2max wrote:
    I need to create a list of random numbers, that do not repeat each other. The range of numbers i will need will be 1 to n. n = any number>1. how can i create a macro to do this.
    abz2max,
    That issue was discussed in the past for several times.
    look for example at:
    http://www.mrexcel.com/board/viewtop...c=4990&forum=2
    Eli

  3. #3
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    God, I really don't want to turn this into a huge discussion like those other two, but this has been nagging at me:

    Dave H suggested the code for picking lottery numbers:


    Function RandLotto(Bottom As Integer, Top As Integer, Amount As Integer)
    Dim iNum As String
    Dim strNum As String
    Dim i As Integer

    Application.Volatile
    iNum = Int((Top - Bottom + 1) * Rnd + Bottom)

    For i = 1 To Amount
    strNum = Trim(strNum & " " & iNum)
    Do Until InStr(1, strNum, iNum) = 0
    iNum = Int((Top - Bottom + 1) * Rnd + Bottom)
    Loop
    Next i

    RandLotto= strNum

    End Function


    I thought about this one day and realised that there is a flaw in this technique. This technique is biased against single digit numbers. That is to say, if "49" came up as the first pick in this code, the numbers "4" and "9" could not be individually picked as a single digit number. This is due to the "instr" check that Dave uses.

    Sorry if this turns into a huge discussion again, but this seemed like a good opportunity to address this without revisiting the old threads.

  4. #4
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Mark,

    Excellent!! Nice catch. Will return unique numbers but is biased.

    Thanks for the post.

    Bye,
    Jay

    P.S. FYI, the following post is, in my opinion, the most amazing of this entire Excel/RNG topic.

    I have specified the link to Harlan Grove's post, but for those interested, please read the whole thread, including Harlan's discussion with Tushar Mehta afterward.

    http://groups.google.com/groups?hl=e...att.net&rnum=5

  5. #5
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Good thread. Interesting to me since I have a vague grasp of bias and such statistical "nonsense". (I'm an engineer, statistics have to be treated carefully in my field)

    The one thing that interests me in Harlan's code is that ahe doesn't use "Randomize" to initialise VB's RNG. Now, I ran into a problem with this myself. I made a little High/Low guessing game in Excel. (kind of like "Play Your Cards Right" and whatever the name of the US version was might have been "Jokers Wild") Anyway, without initialising the RNG I found that if I restarted to play my game again, I got the same "random" numbers each time.

    I think using "Randomize" changes the seed. Again, I don't want to turn this into a hee-yuge discussion, but I know that it's going to head that way.

  6. #6
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,

    I just posted to .worksheetfunctions asking this. I should get a few responses soon, and Harlan will respond if he sees the post.

    Bye,
    Jay

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,169
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    why not find at random.. not produce random?


    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

  8. #8
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Hi,

    Here is Harlan's response:

    Jay Petrulis wrote...
    ....
    >I have one question, for Harlan in particular, about his
    >code. Could it be improved (made "more random") if
    >
    >Randomize
    >
    >is used to initialize the RNG and was included before the
    >VBA Rnd function is invoked, so that the "seed" is
    >reshuffled? Was/is it unnecessary in this instance?
    ....

    It never hurts. Just because I didn't include it doesn't mean I had a reason
    not to include it other than simply forgetting to do so.

    Note: all Rnd calls in all VBA procedures use the same pseudorandom number
    generator, so it's only necessary to call Randomize once in any Excel
    session (though I guess it might be necessary to call it in each additional
    Excel application instance if using multiple Excel sessions - dunno whether
    the state variables are stored in shared DLL memory space or in each
    application instance's own memory space). I call Randomize in Worksheet_Open
    in my Personal.xls file, so I never think of adding it elsewhere.
    Didn't know that one session thing. Calling Randomize when Excel is opened is pretty cool. Adding Randomize to his code would make his routine even better, for those who haven't already initialized the RNG with it.

    Bye,
    Jay

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com