Normsinv function vs standard random normal variable

  • Thread starter Thread starter Jim
  • Start date Start date
J

Jim

What is the difference between:

Normsinv(rand())

and

=rand()+rand()+rand()+rand()+rand()+rand()+rand()+rand()+rand()+rand()+rand(
)+rand() - 6

thank you so much
 
Jim said:
What is the difference between:

Normsinv(rand())

and

=rand()+rand()+rand()+rand()+rand()+rand()+rand()+rand()
+rand()+rand()+rand()+rand() - 6
Click to expand...

Theoretically, the former has infinite support while the latter has finite
support. That is, the former could produce very large magniture results,
positive and negative, especially under some older Excel versions. The
latter will always give a result within (-6;+6). While variance and skewness
are the same, kurtosis and the higher centered moments will differ.
 
Jim said:
What is the difference between:

Normsinv(rand())

and

=rand()+rand()+rand()+rand()+rand()+rand()+rand()+rand()+rand()+rand()
+rand(
)+rand() - 6

thank you so much
Click to expand...
In principle, the normsinv method could give a more precise
approximation to the normal distribution although I am doubtful
whether this is true in Excel. The sum of rand()s is probably adequate
and is the simplest to calculate version of a method that converges on
the answer. In other words, there is nothing sacred about using 12
values. For other numbers, say n, subtract (n/2) and multiply the
value by
SQRT(n/12).
 
James said:
In principle, the normsinv method could give a more precise
approximation to the normal distribution although I am doubtful
whether this is true in Excel.
Click to expand...

....

I agree in versions prior to 2003, but the 2003 normsinv function is
pretty decent. Alternately, you could use Ian Smith's inv_normal function
http://members.aol.com/iandjmsmith/examples.xls
which is comparable to the best statistical programs.

Potentially problematic for the sum of uniforms approach, is the the
fact that the RAND() function is not very good for versions prior to
2003. If you have Excel 2003, you should get the March 23 hotfix

http://www.microsoft.com/downloads/...47-1FAF-4DB6-94CD-4FEC532AC044&displaylang=en

Alternately, you could write a VBA UDF using the 2003 algorithm
http://support.microsoft.com/default.aspx?scid=kb;en-us;828795

Jerry
 
Back
Top