Excel中的RAND（）函数在蒙特卡罗模拟中有多好？ [英] How good is the RAND() function in Excel for Monte Carlo simulation?

问题描述

我正在Excel中的3个变量中实现蒙特卡罗模拟。我使用RAND（）函数从Weibull分布（长尾）中进行抽样。应用于样本的函数是非线性但平滑的（exp，ln，cos等）。每个样本的结果是通过/失败，总体结果是失败的概率。

我也通过数字积分和MathCad中的蒙特卡罗实现了这一点两次得到相同的结果。 MathCad使用（我想）一个Mersenne Twister随机数生成器。

我的excel电子表格得到了一贯不同的结果（即总是更大）。我检查过的方程是一样的。

Excel使用什么随机数生成器，它有多好？这可能是我的问题的根源吗？我已经假设了exp，cos等的Excel实现是可以的。

最后，有没有办法实现蒙特卡罗来减轻（已知）特殊随机数发生器（我听说过马尔可夫链，随机散步等等，但不太了解他们）

非常感谢。

解决方案

McCullough（2008）有一篇关于这个话题的期刊论文：
关于Microsoft Excel 2007（计算统计和数据分析）中统计程序的准确性

引用原始文章：

随机数生成器总是
不足。使用Excel 2003，
Microsoft尝试实现
Wichmann-Hill生成器，并且
未能正确实现。
版本显示在Excel 2007中，但这个
修复完成不正确。 Microsoft已经两次无法实现
正确的
构成Wichmann-Hill
生成器的十行代码;这是任何
本科生计算机科学专业
应该能够做的。 Excel随机
数字生成器不符合用于科学
目的的随机数
生成器的
基本要求：

1. 不知道通过标准随机性测试，例如L'Ecuyer和
   Simard（2007）CRUSH测试（这些
   取代Marsaglia's（1996）DIEHARD
   测试 - 参见Altman等人（2004）a
   比较）;


2. 不知道在
   a中产生大约独立的数字尺寸数量;


3. 它具有未知的句点长度;和


4. 不可重复。

讨论这些要点，请参阅McCullough（2008）的文章
; Excel 2007在这方面的表现不足。

I'm implementing a Monte Carlo simulation in 3 variables in Excel. I've used the RAND() function to sample from Weibull distributions (with long tails). The functions applied to the samples are non-linear but smooth (exp, ln, cos, etc). The result for each sample is a pass/fail, and the overall result is a probability of failure.

I have also implemented this by both numerical integration and Monte Carlo in MathCad, getting the same result both times. MathCad uses (I think) a Mersenne Twister random number generator.

My excel spreadsheet is getting consistently different results (ie always larger). I have checked the equations are the same.

What random number generator does Excel use, and how good is it? Is it possible that this is the source of my problem? I have assumed the Excel implementations of exp, cos etc are ok.

Finally, is there a way to implement Monte Carlo to mitigate against the (known) poor properties of a particular random number generator? (I've heard of Markov chains, random walks etc, but don't really know much about them)

Many thanks.

解决方案

There is a journal paper on this topic by McCullough (2008): On the accuracy of statistical procedures in Microsoft Excel 2007 (Computational Statistics and Data Analysis)

Quoting the original article:

The random number generator has always been inadequate. With Excel 2003, Microsoft attempted to implement the Wichmann–Hill generator and failed to implement it correctly. The fixed version appears in Excel 2007 but this fix was done incorrectly. Microsoft has twice failed to implement correctly the dozen lines of code that constitute the Wichmann–Hill generator; this is something that any undergraduate computer science major should be able to do. The Excel random number generator does not fulfill the basic requirements for a random number generator to be used for scientific purposes:

1. it is not known to pass standard randomness tests, e.g., L’Ecuyer and Simard’s (2007) CRUSH tests (these supersede Marsaglia’s (1996) DIEHARD tests—see Altman et al. (2004) for a comparison);
2. it is not known to produce numbers that are approximately independent in a moderate number of dimensions;
3. it has an unknown period length; and
4. it is not reproducible.

For further discussion of these points, see the accompanying article by McCullough (2008); the performance of Excel 2007 in this area is inadequate.

这篇关于Excel中的RAND（）函数在蒙特卡罗模拟中有多好？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！