Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]()
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
![]() |
|||
|
|||
![]()
Copy this formula down
=INT(RAND()*(10-1)+1) -- Regards, Peo Sjoblom (No private emails please) "Allie" wrote in message ... 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
![]() |
|||
|
|||
![]()
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
![]() |
|||
|
|||
![]()
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" wrote in message ... 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
![]() |
|||
|
|||
![]()
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
![]() |
|||
|
|||
![]()
Peo Sjoblom wrote:
"Allie" 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
![]() |
|||
|
|||
![]()
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
![]() |
|||
|
|||
![]()
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
![]() |
|||
|
|||
![]()
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
![]() |
|||
|
|||
![]()
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" wrote in message ... 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" wrote in message ... 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
![]() |
|||
|
|||
![]()
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" wrote in message ... 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Seed numbers for random number generation, uniform distribution | Excel Discussion (Misc queries) | |||
random numbers, how can you utilize it without repeating them | Excel Discussion (Misc queries) | |||
How to alter data on HTML webpage into Excell cells as numbers? | New Users to Excel | |||
Printing data validation scenarios | Excel Worksheet Functions | |||
Sort pages? | Excel Discussion (Misc queries) |