Is the RANBETWEEN function certified randomness?

We use the RANBETWEEN function to generate random numbers, is this function
certified randomness?

Certified in what sense?

I believe the Microsoft explanation is that it is "random enough".

In other words, for practical purposes, yes.

--JP


On Sep 19, 9:54*pm, Candicehkjc
wrote:
We use the RANBETWEEN function to generate random numbers, is this function
certified randomness?

You can test it depending on the range of numbers you're generating.

For example:

For random numbers from 1 to 10

Select the range A1:A20000
Enter the formula =RANDBETWEEN(1,10)
Hit CTRL ENTER

Enter this formula in C1 and copy down to C10:

=COUNTIF(A:A,ROW())

The initial calculation will take a few seconds to calculate. You will get a
relatively uniform distribution with no extreme outliers.

To generate a new set of data just press F9


--
Biff
Microsoft Excel MVP


"Candicehkjc" wrote in message
...
We use the RANBETWEEN function to generate random numbers, is this
function
certified randomness?

You don't say what you use the random numbers for, or what consequences would
occur if there were detectable autocorrelations. Since you are using
RANDBETWEEN instead of RAND, I will guess that your application is not very
critical, in which case, RANDBETWEEN might be acceptable for your purpose.

RANDBETWEEN appears to correctly convert continuous uniformly distributed
random numbers into discrete uniformly distributed intetegers. The quality
of its output depends on the quality of the underlying random number
generator, which in turn depends on the version of Excel that you are using.

Like almost all software, Excel uses pseudo-random number generators, that
means that the numbers follow a deterministic sequence, instead of being
truly random. Pseudo-random sequences are not all created equal.

Prior to Excel 2007, RANDBETWEEN was part of the Analysis ToolPak (ATP), and
so likely used the ATP random number generator, which was never very good.

Excel 2007 converted ATP functions to native worksheet functions. In 2007,
RANDBETWEEN likely calls the worksheet RAND function. MS changed the
algorithm for RAND in 2007. It is now supposed to use an algorithm that was
considered to be quite good 25 years ago, but is not considered to be up to
today's standards for serious applications. More disturbing, it does not
appear to be implemented correctly despite being a very simple algorithm.
The original 2007 implementation sometimes produced negative numbers, which
is not possible in the algorithm they intended to use. MS issued a patch
that to resolve the negative number problem but a recent research article
notes that the output of RAND in 2007 is still not consistent with the
algorithm that MS claims to use
http://groups.google.com/group/micro...4bc52422a1ca99
Consequently, no one knows how good or bad the 2007 random number generator
is because no on knows how MS is actually producing its random numbers.

Jerry

"Candicehkjc" wrote:

We use the RANBETWEEN function to generate random numbers, is this function
certified randomness?

There's a short description he

http://support.microsoft.com/kb/828795

--JP

On Sep 21, 8:26*pm, Jerry W. Lewis wrote:
Consequently, no one knows how good or bad the 2007 random number generator
is because no on knows how MS is actually producing its random numbers.

Jerry

Your cited article gives the method that MS claims to use in 2007, but what
they actually use remains a mystery, since as I previously noted, McCullough
recently showed (Computational Statistics & Data Analaysis 52(10):4587-4593,
2008) that the sequence produced by RAND is not consistent with these
formulas. A preprint can be downloaded from
http://citeseerx.ist.psu.edu/viewdoc...10.1.1.93.9671

Jerry

"JP" wrote:

There's a short description he

http://support.microsoft.com/kb/828795

--JP

On Sep 21, 8:26 pm, Jerry W. Lewis wrote:
Consequently, no one knows how good or bad the 2007 random number generator
is because no on knows how MS is actually producing its random numbers.

Jerry

Sorry, to get the article, you can purchase it from
http://www.sciencedirect.com/science...d5c161cdf162c5

Jerry

"Jerry W. Lewis" wrote:

Your cited article gives the method that MS claims to use in 2007, but what
they actually use remains a mystery, since as I previously noted, McCullough
recently showed (Computational Statistics & Data Analaysis 52(10):4587-4593,
2008) that the sequence produced by RAND is not consistent with these
formulas. A preprint can be downloaded from
http://citeseerx.ist.psu.edu/viewdoc...10.1.1.93.9671

Jerry

"JP" wrote:

There's a short description he

http://support.microsoft.com/kb/828795

--JP

On Sep 21, 8:26 pm, Jerry W. Lewis wrote:
Consequently, no one knows how good or bad the 2007 random number generator
is because no on knows how MS is actually producing its random numbers.

Jerry

Jerry,

I think we can agree that, for scientific purposes, Excel isn't
equipped to generate random numbers the way some people need. For most
of us, the functionality provided is sufficient.


On Sep 23, 6:37*am, Jerry W. Lewis wrote:
Your cited article gives the method that MS claims to use in 2007, but what
they actually use remains a mystery, since as I previously noted, McCullough
recently showed (Computational Statistics & Data Analaysis 52(10):4587-4593,
2008) that the sequence produced by RAND is not consistent with these
formulas. *A preprint can be downloaded fromhttp://citeseerx.ist.psu.edu/viewdoc/summary;jsessionid=D503F02718960...

Jerry