Random Sampling

[Roger]

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.

 

[Alex]

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

 

[Jerry W. Lewis]

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/[email protected]

implements the 2003 algorithm in VBA.

An even better algorithm is the Mersenne Twister

http://www.math.sci.hiroshima-u.ac.jp/~m-mat/MT/emt.html
http://www.math.sci.hiroshima-u.ac.jp/~m-mat/MT/VERSIONS/FORTRAN/fortran.html
http://www-personal.engin.umich.edu/~wagnerr/MersenneTwister.html

which is implemented in the freeware NtRand

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

Jerry

 

[Andrea]

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

 

[Peo Sjoblom]

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

 

[bplumhoff]

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