What algorithm does Excel 2010 use for Pseudo Random Number Generation (MT19937?)

Does Excel 2010+ use the Mersenne Twister (MT19937) algorithm for Pseudo Random Number Generation (PRNG), implemented by the RAND() function?

This has been a nagging question for some time now, with "hints" that it indeed does.  However, a relatively thorough search turns up no definitive documentation.  The most direct indication is perhaps given by Guy Melard [Ref 9] where he tests Excel 2010's RAND() function using the Crush battery of tests in TestU01 by L'Ecuyer & Simard.  Melard references a "semi-official" indication that Microsoft did indeed implement MT19937 for the RAND() function in Excel 2010, but this reference no longer seems to be available. http://office.microsoft.com/enus/excel-help/about-solver-HP005198368.aspx?pid=CH010004571033.

The other references below [Ref 1-10] document the history of the statistical suitability of the PRNG and probability distributions in various versions of Excel.  This includes the Wichmann-Hill PRNG implementations supposedly (arguably) used in Excel 2003 & 2007 for random number generation.  But still, we have no answer as to which PRNG algorithm is used in Excel 2010 (and 2013 for that matter).

Microsoft indicates that RAND() has been improved in Excel 2010; Microsoft states, "...and the RAND function now uses a new random number algorithm." (see https://support.office.com/en-ca/article/Whats-New-Changes-made-to-Excel-functions-355d08c8-8358-4ecb-b6eb-e2e443e98aac).  But no details are given on the actual algorithm.  This is critical for Monte Carlo methods and many other applications.

Any help would be much appreciated. Thanks.

[Ref 1] B. McCullough, B. Wilson.  On the Accuracy of Statistical Procedures in Microsoft Excel 97.  Computational Statistics & Data Analysis. Vol. 31 No. 1, pp 27-37. July 1999. http://users.df.uba.ar/cobelli/LaboratoriosBasicos/excel97.pdf

[Ref 2]L. Knsel.  On the accuracy of the statistical distributions in Microsoft Excel 97. Computational Statistics & Data Analysis. Vol. 26 No. 3, pp 375-377. January 1998. http://www.sciencedirect.com/science/article/pii/S0167947397817562

[Ref 3]B. McCullough, B. Wilson.  On the Accuracy of Statistical Procedures in Microsoft Excel 2000 and Excel XP.  Computational Statistics & Data Analysis. Vol.40 No. 4, pp 713-721. October 2002. https://www.researchgate.net/publication/222672996_On_the_accuracy_of_statistical_procedures_in_Microsoft_Excel_2000_and_Excel_XP/links/00b4951c314aac4702000000.pdf

[Ref 4] B. McCullough, B. Wilson.  On the Accuracy of Statistical Procedures in Microsoft Excel 2003.  Computational Statistics & Data Analysis. Vol.49. No. 4, pp 1244-1252. June 2005. http://www.pucrs.br/famat/viali/tic_literatura/artigos/planilhas/msexcel.pdf

[Ref 5] L. Knsel. On the accuracy of statistical distributions in Microsoft Excel 2003. Computational Statistics & Data Analysis, Vol. 48, No. 3, pp 445-449. March 2005. http://www.sciencedirect.com/science/article/pii/S0167947304000337

[Ref 6]B. McCullough, D.Heiser.  On the Accuracy of Statistical Procedures in Microsoft Excel 2007.  Computational Statistics & Data Analysis. Vol.52. No. 10, pp 4570-4578. June 2008. http://users.df.uba.ar/mricci/F1ByG2013/excel2007.pdf

[Ref 7] A. Yalta. The Accuracy of Statistical Distributions in Microsoft<sup></sup> Excel 2007. Computational Statistics & Data Anlaysis. Vol. 52 No. 10, pp 4579 4586. June 2008. http://www.sciencedirect.com/science/article/pii/S0167947308001618

[Ref 8] B. McCullough.  Microsoft Excels Not The Wichmann-Hill Random Number Generators. Computational Statistics and Data Analysis. Vol.52. No. 10, pp 4587-4593. June 2008. http://www.sciencedirect.com/science/article/pii/S016794730800162X

[Ref 9] G. Melard.  On the Accuracy of Statistical Procedures in Microsoft Excel 2010. Computational Statistics. Vol.29 No. 5, pp 1095-1128. October 2014. http://homepages.ulb.ac.be/~gmelard/rech/gmelard_csda23.pdf

[Ref 10] L. Knsel.  On the Accuracy of Statistical Distributions in Microsoft Excel 2010. Department of Statistics - University of Munich, Germany. http://www.csdassn.org/software_reports/excel2011.pdf

April 1st, 2015 10:34am

Bump for help on this. Thx.
Free Windows Admin Tool Kit Click here and download it now
April 7th, 2015 10:15am

Apologies for bumping again.  This is important to many researchers and Microsoft apparently cannot provide a direct answer.  I have filed an official MS Support Request, but it just keeps getting cancelled without resolution.  Any help or insight would be much appreciated.  Thanks--

April 15th, 2015 1:38pm

Interesting question! Not sure I can help except perhaps to remove your 'arguably' doubt about 2003/2007, the change in 2003 is documented here

Description of the RAND function in Excel

I know, you'll be wondering if the author of the what's-new-in-2010-page really did know it had been changed yet again, or is it still the same since 2003!

Free Windows Admin Tool Kit Click here and download it now
April 15th, 2015 4:32pm

I found the same KB article:

https://support.microsoft.com/en-us/kb/828795

This was introduced (according to the article) in Excel 2003. Perhaps the references in notes 2 and 3 might help.

The article describes combining the results of 3 generators, each similar to a Multiply With Carry (MWC) generator, but with zero carry. MWC generators do very well on the Diehard battery of randomness tests (mentioned in your references), and have very long periods. But using zero carry makes no sense to me.

Combining the three generators only helps if the periods of the 3 are relatively prime (despite what the article implies). Then the period of the result will be the product of the 3 periods. But without knowing the theory behind these generators, I have no idea what the periods would be. The formulas for MWC generators fail

April 15th, 2015 6:16pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics