NormInv performace in Excel 2003

I have compared the speed performance of Excel 2003 vs. Excel 2000 in
generating series of normally distributed random numbers.

Sub test()
ActiveSheet.Range("A1") = Time()
For i = 1 To 64000
ActiveSheet.Range("A" & i + 1).Value = Application.NormInv(Rnd(), 0, 1)
Next i
ActiveSheet.Range("B1") = Time()
End Sub


I get that Excel 2000 is 5.5 faster.
Does anybody experiance the same problem?
I wonder if the problem is due to the upgrade of the NormInv function
in Excel 2003 or there is any other reason.

Thanks,
Michael

Your comparison is apples to oranges, since you will likely be unhappy
with the result of generating normal random numbers this way in Excel
2000. In Excel 2000, ABS(NormsInv(p)) = 5E6 for Min(p,1-p)< 3E-7.
Under normality, the probability of seeing ABS(x)>=5E6 is less than
2*10^(-5.4E12). To put this in context, the smallest number that Excel
can distinguish from zero is about 10^308, whereas 2*10^(-5.4E12) is
about 10^(thirty-five trillion) times smaller!

Normal random numbers that are five million standard deviations from the
mean are clearly wrong, and could be culled if you are not overly
concerned about the extreme tail of the distribution that you are
generating. However less obviously wrong values will also be distorted
away from normality.

You could use an accurate VBA inverse normal distribution, such as
http://groups-beta.google.com/group/...38d6ddefaed7be
or
http://members.aol.com/iandjmsmith/examples.xls
but I guarantee that NormInv in 2003 will be faster.

Side issue: if you will always be generating standard normal numbers,
why not use NormSInv instead of NormInv?

Jerry

webshark wrote:

> I have compared the speed performance of Excel 2003 vs. Excel 2000 in
> generating series of normally distributed random numbers.
>
> Sub test()
> ActiveSheet.Range("A1") = Time()
> For i = 1 To 64000
> ActiveSheet.Range("A" & i + 1).Value = Application.NormInv(Rnd(), 0, 1)
> Next i
> ActiveSheet.Range("B1") = Time()
> End Sub
>
>
> I get that Excel 2000 is 5.5 faster.
> Does anybody experiance the same problem?
> I wonder if the problem is due to the upgrade of the NormInv function
> in Excel 2003 or there is any other reason.
>
> Thanks,
> Michael

Jerry,

Thank you for the reply.

Michael

You're welcome.

Jerry

webshark wrote:

> Jerry,
>
> Thank you for the reply.
>
> Michael