Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is the RANBETWEEN function certified randomness?
We use the RANBETWEEN function to generate random numbers, is this function
certified randomness? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is the RANBETWEEN 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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is the RANBETWEEN 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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is the RANBETWEEN 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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is the RANBETWEEN 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is the RANBETWEEN function certified randomness?
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is the RANBETWEEN function certified randomness?
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is the RANBETWEEN function certified randomness?
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy of excel file not showing formulal/function in the function b | Excel Discussion (Misc queries) | |||
Do gettinf certified give you a edge in the work place? | Excel Discussion (Misc queries) | |||
Template for Certified or Wage Decision Payrolls | Excel Discussion (Misc queries) | |||
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
Offset function with nested match function not finding host ss. | Excel Worksheet Functions |