I am trying to extract a random sample of 150 numbers from a list of 600 from
the statistical analysis tool.

The problem that I'm having is that the sample keeps coming up with
duplicate numbers.

Can anyone tell me how I can eliminate these duplicates?

Any help would be appreciated

"Jerry W. Lewis" wrote:

The algorithm used depends on your Excel version

http://support.microsoft.com/kb/q86523/
http://support.microsoft.com/kb/q828795/

It also appears that RNGs in the worksheet, Analysis ToolPak, and VBA
each uses a different algorithm. ATP, VBA and pre 2003 worksheet
algorithms are not suitable for serious simmulation work.

http://groups.google.com/groups?selm...g .google.com

implements the 2003 algorithm in VBA.

An even better algorithm is the Mersenne Twister

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

which is implemented in the freeware NtRand

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

Jerry

Alex wrote:

Roger

I do not know specifically what algorithm Excel uses to generate a random
sample, but in general, many computers use their 'internal clock' as a source
of a 'random' input and then enter it into an algorithm to come up with a
random sample.

Alex

"Roger" wrote:


When you use the Data Analysis tool, Random Sampling, is there a way to see
how excel comes up with the figures. Ex. You have a set of 5 numbers
1,2,3,4,5. You run a random sample and ask it to output 1 value and excel
spits out 2. Is there a way to see how excel came up with that number? Is
there some behind the scene code that does it or maybe a formula? Any help
would be appreciated.

http://mcgimpsey.com/excel/udfs/randint.html

--

Regards,

Peo Sjoblom

"Andrea" wrote in message
...
I am trying to extract a random sample of 150 numbers from a list of 600
from
the statistical analysis tool.

The problem that I'm having is that the sample keeps coming up with
duplicate numbers.

Can anyone tell me how I can eliminate these duplicates?

Any help would be appreciated

"Jerry W. Lewis" wrote:

The algorithm used depends on your Excel version

http://support.microsoft.com/kb/q86523/
http://support.microsoft.com/kb/q828795/

It also appears that RNGs in the worksheet, Analysis ToolPak, and VBA
each uses a different algorithm. ATP, VBA and pre 2003 worksheet
algorithms are not suitable for serious simmulation work.


http://groups.google.com/groups?selm...g .google.com

implements the 2003 algorithm in VBA.

An even better algorithm is the Mersenne Twister

http://www.math.sci.hiroshima-u.ac.j...at/MT/emt.html

http://www.math.sci.hiroshima-u.ac.j...N/fortran.html
http://www-personal.engin.umich.edu/...neTwister.html

which is implemented in the freeware NtRand

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

Jerry

Alex wrote:

Roger

I do not know specifically what algorithm Excel uses to generate a
random
sample, but in general, many computers use their 'internal clock' as a
source
of a 'random' input and then enter it into an algorithm to come up
with a
random sample.

Alex

"Roger" wrote:


When you use the Data Analysis tool, Random Sampling, is there a way
to see
how excel comes up with the figures. Ex. You have a set of 5 numbers
1,2,3,4,5. You run a random sample and ask it to output 1 value and
excel
spits out 2. Is there a way to see how excel came up with that
number? Is
there some behind the scene code that does it or maybe a formula? Any
help
would be appreciated.

Hello Andrea,

You can take my user-defined-function
http://www.sulprobil.com/html/uniqrandint.html

If you want unique random integers let the parameter lMaxOccurrence
stay = 1, if you wwant to allow repetitions, define with this parameter
how many are allowed.

HTH,
Bernd