Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
NormInv performace in Excel 2003
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
NormInv performace in Excel 2003
Jerry,
Thank you for the reply. Michael |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
NormInv performace in Excel 2003
You're welcome.
Jerry webshark wrote: Jerry, Thank you for the reply. Michael |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 versus Excel 2003 & Excel 97-2003 fully compatible | Excel Worksheet Functions | |||
Does installing Service Packs help with Excel performace? | Excel Discussion (Misc queries) | |||
Copying Excel 2003 Selection into Outlook 2003 HTML E-Mail Message | Excel Discussion (Misc queries) | |||
VBA and norminv | Excel Programming |