forcing unique random numbers - Page 2
Amazing chart utilities from Jon Peltier
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 22

Thread: forcing unique random numbers

  1. #11
    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 Jack,

    I think we are discussing two different things. I believe you are trying to get a random number generator which is truly random, while I am reporting some procedures which will result in no duplicate values.

    Dave's code resamples if a duplicate is found, so while not technically uniquely random, it overcomes it nicely.

    I've read that the period for the VBA RND() function is 2^24, so for most work it should be OK. When you truncate or round to integers, you will encounter some duplicates.

    The Microsoft KB article:
    http://support.microsoft.com/support...s/Q86/5/23.ASP

    shows the RAND() algorithm to produce about 1 million numbers.

    Using Randomize before the RND() in VBA should enable you to get "randomness" to a high degree of statistical precision.

    Another random selector from Laurent Longre is shown. This one reads the values into an array, so it would be good within the VBA environment.

    ---------------------------
    Option Base 1
    Dim Res() As Integer
    Dim I As Integer
    Dim J As Integer
    Dim Transit As Integer
    Const NBVALS = 15

    ReDim Res(NBVALS)
    For I = 1 To NBVALS
    Res(I) = I
    Next I
    For I = 1 To NBVALS - 1
    J = Int(Rnd() * (NBVALS - I + 1)) + I
    Transit = Res(I)
    Res(I) = Res(J)
    Res(J) = Transit
    Next I
    -----------------------------

    If you are looking for normal variates,
    =norminv(rand(),mean,stdev)
    should be OK for most work.

    If you want to see what I believe is the pinnacle of correcting Excel's problems with this, see Jerry Lewis's code for the PNorm at

    http://groups.google.com/groups?selm...0no_e-mail.com

    Also, please post whatever you have.

    Thanks,
    Jay

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

    Default

    Have a look at rand_unique at :-

    http://www.xl-logic.com/pages/formulas.html

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

    Default

    Is that not waht you asked in the subjust .. unique random numbers..


    exceptable randon is OK yes there are qways as shown VBA will easy do this but soory if i git it wrong mut i read UNIQUE as NEVER a repeat.. right or wrong thats my take, thus the comments.

    at all wahts exceptable containg i see as not random, randon can never repeat ..

    buts its you decisions in not disagreeing but im not agreeing, such is the fun of this board we all learn things, i have learnt exceptable can be OK and no need to progress further to buzlight year enfinity and beyond, which was my call, just a view,

    I have VBA in lotto numbers which ill post ASAP it works on FIND not rand which again is my thinking to random, why do i have to produce randon numbers = can i not select them at random..... same result but floorless.

    I would love to post my scripts but i dont even have excel loaded just XP Windows and IE so sadly i can, not even my email, im testing XP horrid thing.. pester me ill post ASAP.

    If you win, treat some guys less off than us, thats cool dont you think?



    Free Excel based Web Toolbar available here.

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

  4. #14
    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

    april 15th.

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

    Default

    ERrmm.. looks like that is solved and the matter of non repeats is addressed, not being difficult, but i must say Dave has hit on something, Excel if programmed can be forced to detect duos and recal.. thus ebd result correct... Mark you seem to have over come this with nice code, i cant test as no Excel, still texting Win XP, youck!

    but i will and ill post what i find, very intresting and many will have learnt, i guess not back to fomula to do this also, and again must be random, and not predicatable.

    random is as BuzLight Years says infinaty and beyond, but contain it ie 1-49 req 6 intergers??? randon between this must be as you have displayed.. food for thought, we have formula that will give 1 billion to 1 chance or repeat but thats not exceptable, needs to be NONE in each run...

    good stuff

    Free Excel based Web Toolbar available here.

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

  6. #16
    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 Jack and Mark,

    When you are talking about Dave Hawley's RandLotto function returning duplicate values, are you saying

    1. That there is a chance that the same set of six numbers will appear more than once if the simulation is run x number of times, where x is too low a number to be more than coincidental?

    2. Or, are you saying that an individual draw of six could produce a duplicate?

    If it is 2 above, doesn't the loop preclude that, where he resamples until the matches are zero?

    I don't understand how these duplication issues occur in these functions/procedures.

    In the various threads, we have:

    1. RandLotto function
    2. HGSample function
    3. HGSample2a function
    4. Tom Ogilvy's sub
    5. Mark's Main/RandOrder function
    6. RandUnique link from the prior post in this thread

    There are six different options, but you are guaranteeing only one of them. Why not the other five? I don't understand where the duplicates arise. Please advise.

    BTW, Mark, excellent job on the routine. Very nice post.

    Bye,
    Jay

  7. #17
    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

    Apologies and thanks for making me look at Dave's code again Jay. I was expecting an "If" statement, but on my first glance I didn't notice his rather snifty use if "Instr".

    I declare Dave the winner. Unless my code is faster. (Which I doubt) I really just wanted to say that old trick of using an array anyway.

  8. #18
    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,

    No apologies needed. These were terrific efforts by you and Dave and the others. These were great threads, too, with great questions and discussions.

    *All* of these are in my keeper file.

    Bye,
    Jay

  9. #19
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    It has been a pretty informative discussion. Practically speaking, I need to generate say 50 questions from a bank of several hundred questions for a test. I do not want any two tests to be alike. So, I want to have the 50 questions selected and ordered randomly. I go through several steps to accomplish this.
    First I select more than 50 questions using ...
    TOOLS|DATA_Analysis|Sampling ... then Input Range, and the number of samples -- there are repeats in this list
    Then I filter this list to select 50 unique numbers

    This may not be the most efficient way, nor it may be truly random, but I just wanted to share with you my approach.

    _________________
    Yogi Anand
    Edit: Deleted reference to inactive web site from signature line




    [ This Message was edited by: Yogi Anand on 2003-01-19 18:15 ]

  10. #20

    Join Date
    Mar 2002
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Here's another way of doing it, without using a loop. This should be quicker (but not checked for this).
    It is based on the method used (Rand_Unique) at http://www.xl-logic.com/pages/formulas.html as suggested by "toofoo".
    A macro could be created with the macro recorder (no VBA knowledge required).

    Sub RandomOrder()
    Application.ScreenUpdating = False
    With [B1]
    .Value = 1
    .AutoFill Destination:=[B1:B233], Type:=xlFillSeries
    End With
    [A1:A233].Formula = "=RAND()"
    Columns("A:B").Sort Key1:=[A1]
    Columns(1).Delete
    End Sub


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