+ Reply to Thread
Results 1 to 11 of 11

Non-random numbers generated by excel's data analysis random gener

  1. #1
    Allie
    Guest

    Non-random numbers generated by excel's data analysis random gener

    Hi,

    I've been trying to generate random numbers using excel's random number
    generator in the analysis toolpak. I'm using Uniform number generation
    between 0 and 1, and I am using a random seed, as I would like to be able to
    re-generate the same numbers in the future.

    Using seeds from 1 to 10, when i fill a range of 16 cells with random
    numbers, the first cell filled is always the lowest. This is an extremely
    unlikely circumstance with a true RN generator. I just wanted to raise this
    flag, as it could be affecting others without their knowledge.

    Thanks,
    Allie


  2. #2
    Peo Sjoblom
    Guest

    Re: Non-random numbers generated by excel's data analysis random gener

    Copy this formula down

    =INT(RAND()*(10-1)+1)

    --
    Regards,

    Peo Sjoblom

    (No private emails please)


    "Allie" <Allie@discussions.microsoft.com> wrote in message
    news:022013BF-36F2-4F09-9244-1E6FA3340BFF@microsoft.com...
    > Hi,
    >
    > I've been trying to generate random numbers using excel's random number
    > generator in the analysis toolpak. I'm using Uniform number generation
    > between 0 and 1, and I am using a random seed, as I would like to be able
    > to
    > re-generate the same numbers in the future.
    >
    > Using seeds from 1 to 10, when i fill a range of 16 cells with random
    > numbers, the first cell filled is always the lowest. This is an extremely
    > unlikely circumstance with a true RN generator. I just wanted to raise
    > this
    > flag, as it could be affecting others without their knowledge.
    >
    > Thanks,
    > Allie
    >



  3. #3
    Allie
    Guest

    Re: Non-random numbers generated by excel's data analysis random g

    Thanks for your reply, Peo. I'm not looking for a workaround (which I've
    already figured out), I'm interested in letting people, and microsoft, know
    about this bug.

    Thanks,
    Allie

  4. #4
    Mike Middleton
    Guest

    Re: Non-random numbers generated by excel's data analysis random gener

    Allie -

    See "Description of the effects of the improved statistical functions for
    the Analysis ToolPak in Excel 2003 and in Excel 2004 for Mac," at

    http://support.microsoft.com/default...b;en-us;829208

    specifically, the "Random Number Generation" section.

    - Mike
    www.mikemiddleton.com

    "Allie" <Allie@discussions.microsoft.com> wrote in message
    news:022013BF-36F2-4F09-9244-1E6FA3340BFF@microsoft.com...
    > Hi,
    >
    > I've been trying to generate random numbers using excel's random number
    > generator in the analysis toolpak. I'm using Uniform number generation
    > between 0 and 1, and I am using a random seed, as I would like to be able
    > to
    > re-generate the same numbers in the future.
    >
    > Using seeds from 1 to 10, when i fill a range of 16 cells with random
    > numbers, the first cell filled is always the lowest. This is an extremely
    > unlikely circumstance with a true RN generator. I just wanted to raise
    > this
    > flag, as it could be affecting others without their knowledge.
    >
    > Thanks,
    > Allie
    >




  5. #5
    joeu2004@hotmail.com
    Guest

    Re: Non-random numbers generated by excel's data analysis random gener

    Allie wrote:

    > I've been trying to generate random numbers using excel's random number
    > generator in the analysis toolpak. I'm using Uniform number generation
    > between 0 and 1, and I am using a random seed, as I would like to be able to
    > re-generate the same numbers in the future.
    >
    > Using seeds from 1 to 10, when i fill a range of 16 cells with random
    > numbers, the first cell filled is always the lowest. This is an extremely
    > unlikely circumstance with a true RN generator.


    Not at all.

    First, because you are seeding the RNG, it is not
    "a true RNG" at all. The sequence is predictable
    and repeatable, which is your intention.

    Second, if we knew the RNG algorithm, it might not
    be surprising at all that a low seed (1 to 10) might
    generate a very low number initially, perhaps even
    the lowest value a small set (16) of numbers.

    In fact, if you increase the set to 100 with a seed
    of 10, the first number is not the smallest. The
    same is true of a set of 16 numbers if you choose a
    large seed, e.g. 10000.

    MS Excel documentation should offer some guidance for
    choosing a seed. It can greatly affect not only the
    range of numbers generated, but also the apparent
    randomness (distribution over the range) of at least
    the first small set of numbers. I do not find any
    such guidance in the Help text.

    In any case, all of this is common to all RNGs. An
    RNG simply uses a recursive mathematical formula.
    For example, a linear congruential method might use
    the formula X[n+1] = (a*X[n] + c) mod m, where X[0]
    is the seed or a deterministic function of the seed
    (apparently the latter for the ATP RNG, since its
    seed must be an integer), and a, c and m are chosen
    by the RNG designer, hopefully based on the plethora
    of research literature on the subject. For an
    introduction, see Knuth, Art of Computer Programming,
    vol 2 (Seminumerical Algorithms).

    I hope this gives you some insight into your observation.
    If it bothers you (I don't know why it would), choose
    a different seed or generate a larger set of numbers.


  6. #6
    joeu2004@hotmail.com
    Guest

    Re: Non-random numbers generated by excel's data analysis random gener

    Peo Sjoblom wrote:
    > "Allie" <Allie@discussions.microsoft.com> wrote:
    > > I'm using Uniform number generation between 0 and 1,
    > > and I am using a random seed, as I would like to be
    > > able to re-generate the same numbers in the future.
    > >
    > > Using seeds from 1 to 10, when i fill a range of 16
    > > cells with random numbers

    >
    > Copy this formula down
    > =INT(RAND()*(10-1)+1)


    Obviously you do not understand what a "seed" is for an
    RNG.

    Allie is not trying to generate random integers between
    1 and 10, which your expression does. In fact, she said
    she wants to generate random real numbers between 0 and 1.

    A "seed" is a value that is used to initialize an RNG
    so that the sequence is repeatable -- as Allie said.
    Your expression generates a non-repeatable sequence of
    numbers -- hopefully ;-).


  7. #7
    D Hilberg
    Guest

    Re: Non-random numbers generated by excel's data analysis random gener

    Anyone who still uses numerical methods to generate random numbers is
    living in a state of sin.

    (according to John von Neuman)

    - D Hilberg

    (Have you tried measuring the decay of a radioactive element?)


  8. #8
    Jerry W. Lewis
    Guest

    Re: Non-random numbers generated by excel's data analysis random gener

    D Hilberg wrote:

    > Anyone who still uses numerical methods to generate random numbers is
    > living in a state of sin.
    >
    > (according to John von Neuman)
    >
    > - D Hilberg
    >
    > (Have you tried measuring the decay of a radioactive element?)



    And you measure the decay of radioactive elements from your living room how?

    pRNG algorithms have progressed greatly since von Neumann's time. Excel
    2003's RAND(), which is a vast improvement, is still two decades out of
    date. The Mersenne Twister is quite a good pRNG

    http://www.math.sci.hiroshima-u.ac.j...at/MT/emt.html
    http://www.math.sci.hiroshima-u.ac.j...S/FORTRAN/fort...
    http://www-personal.engin.umich.edu/...neTwister.html

    which is implemented in the freeware NtRand

    http://www.numtech.com/NtRand/

    Jerry


  9. #9
    joeu2004@hotmail.com
    Guest

    Re: Non-random numbers generated by excel's data analysis random gener

    D Hilberg wrote:
    > Anyone who still uses numerical methods to generate random
    > numbers is living in a state of sin.
    > [....]
    > (Have you tried measuring the decay of a radioactive element?)


    At the risk of giving credance to what might have been
    intended as levity ....

    Yes, there are hardware-based RNGs. These are becoming
    common-place on "mainframes" (technical computers). They
    might be available as add-ons for PCs. I did not think
    they are commonly built into PCs, though. Are they?

    In any case, the OP asked about a seeded RNG. By
    definition, a seeded RNG is deterministic (repeatable),
    not "random" at all (although we hope a large sampling
    has the appearance of "randomness"). And whether you
    rely hardware or software, the first number of a seeded
    RNG will depend on the seed, by definition.

    The purpose of my posting was to provide a basic
    understanding of seeded RNGs so that the OP could
    understand why the first number in a small sampling
    __might__ "always" be the smallest value, without the
    illusion of a defect. And in fact, my point was: it
    will __not__ always be the smallest, if you choose
    either the seed or the sample size appropriately.


  10. #10
    David J. Braden
    Guest

    Re: Non-random numbers generated by excel's data analysis random gener

    Hey Mike!
    OK, OK, others interested in this kind of stuff---
    The link http://support.microsoft.com/kb/828795 glosses over Excel's latest
    incarnation of RAND()

    (1) We now have a doc of what MS intends. Can anyone map back into the longs
    from doubles to run the PRNG through Marsaglia's DIEHARD? (I can't) The
    coders might be trying to fix a long established problem (I started
    complaining in 1985). Do we *know* that they actually implemented what they
    intended this last go-round (as opposed to what shipped with Excel 11) with
    the latest SP? Also, *when* will they get a seed into the thing? (see note)

    (2) Microsoft's note re ATP PRNG is absolute BS. Generate a few thousand
    variates, and you will see repeats, including a number of 0's and 1's.

    (3) What's up with VBA's RND? Any sign of an impending upgrade?

    Regards,
    Dave B

    "Mike Middleton" <middleton@mikemiddleton.com> wrote in message
    news:uyOzFDQuFHA.2624@TK2MSFTNGP12.phx.gbl...
    > Allie -
    >
    > See "Description of the effects of the improved statistical functions for
    > the Analysis ToolPak in Excel 2003 and in Excel 2004 for Mac," at
    >
    > http://support.microsoft.com/default...b;en-us;829208
    >
    > specifically, the "Random Number Generation" section.
    >
    > - Mike
    > www.mikemiddleton.com
    >
    > "Allie" <Allie@discussions.microsoft.com> wrote in message
    > news:022013BF-36F2-4F09-9244-1E6FA3340BFF@microsoft.com...
    >> Hi,
    >>
    >> I've been trying to generate random numbers using excel's random number
    >> generator in the analysis toolpak. I'm using Uniform number generation
    >> between 0 and 1, and I am using a random seed, as I would like to be able
    >> to
    >> re-generate the same numbers in the future.
    >>
    >> Using seeds from 1 to 10, when i fill a range of 16 cells with random
    >> numbers, the first cell filled is always the lowest. This is an
    >> extremely
    >> unlikely circumstance with a true RN generator. I just wanted to raise
    >> this
    >> flag, as it could be affecting others without their knowledge.
    >>
    >> Thanks,
    >> Allie
    >>

    >
    >




  11. #11
    Dana DeLouis
    Guest

    Re: Non-random numbers generated by excel's data analysis random gener

    Hi Alice. I believe this is a known problem. The first number in the list
    follows a linear relationship with the seed.
    With a 16 number output, the first number output runs from about 0 to 1 with
    seeds 1 to about 10022. (It's a near perfect correlation. R^2 =
    1.000000)With a seed of 10023, the first number drops back to near zero and
    repeats the cycle.
    So, when you seed with low numbers, there is little chance that another
    number will be lower than the first one. As the seeds get larger, the first
    number gets larger, and there are more chances that other numbers will be
    lower than the first.

    I was just curious, so I plotted the percentages that the first number was
    the lowest out of 16. The plot starts out near 95% - 100% for a few hundred
    numbers, and then exponentially decays to about 8% when reaching the largest
    seed of 32767. Because of the "Bug", (err...I mean feature) it will never
    get to the expected 6.25% (1/16).

    If you would like to see a listing of the first number in the output, here
    is a macro. There is no need to see all 32767, as the first few hundred
    will show you the relationship.
    Set a vba library reference to "atpvbaen.xls"
    This takes a few minutes to run.

    Here, the "C" is used as a seed number, and as a column pointer to store the
    first output cell.

    Sub Curious()
    Dim C As Long
    Const Random As String = "ATPVBAEN.XLA!Random"

    [A:C].Clear

    For C = 1 To 11000 '32767
    [A1:A16].Clear
    Run Random, Cells(1, 1), 1, 16, 1, C, 0, 1
    Cells(C, 3) = Cells(1, 1)
    Application.StatusBar = C
    Next C
    End Sub

    HTH :>)
    --
    Dana DeLouis
    Win XP & Office 2003


    "Allie" <Allie@discussions.microsoft.com> wrote in message
    news:022013BF-36F2-4F09-9244-1E6FA3340BFF@microsoft.com...
    > Hi,
    >
    > I've been trying to generate random numbers using excel's random number
    > generator in the analysis toolpak. I'm using Uniform number generation
    > between 0 and 1, and I am using a random seed, as I would like to be able
    > to
    > re-generate the same numbers in the future.
    >
    > Using seeds from 1 to 10, when i fill a range of 16 cells with random
    > numbers, the first cell filled is always the lowest. This is an extremely
    > unlikely circumstance with a true RN generator. I just wanted to raise
    > this
    > flag, as it could be affecting others without their knowledge.
    >
    > Thanks,
    > Allie
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1