
#21




I use RAND() to generate a seqence of random numbers, and then I save them in the spreadsheet. All the other calculations depend on those saved values. What's the problem?

#22




Quote:
You create a set of random numbers using a simple macro: 1. Calc; 2. Copy RAND() cell; 3. PSV to row x; 4. x = x+1; GOTO 1 5. End when you feel like it. Is this difficult? I don't think so.
__________________
"Facebook is a toilet."  LWTwJO "45 es un titere"  Seal of The President of The United States of America protest art 
#24




Quote:
If you MUST use Excel (as I did at work), avoid RAND like the plague. Use rnd in VBA. My limited testing of Monte Carlo simulations with vba generally indicated that the VB LCG (used in vba) produced good results for moderate sized samples, where "moderate" really depends on the sophistication for what you are trying to do with the results. There is at least one Excel addin I have seen uses the Mersenne Twister, which is a heavy duty RNG suitable, I suspect, for just about anything you want to do. That was a product called ZRandom, created by the AO's Banquet of Chestnuts. http://www.zrandom.com/zrand/ Disclaimer: I have no connection with either the product or its developer/company.
__________________
Brad Gile, FSA, MAAA Affiliate Member of the CAS Dedicated Retired Actuary Spoiler: Spoiler: 
#25




I think that being able to reproduce a list of random numbers would prove that they aren't random. Typical trick question by an auditor, designed to prolong the hourlyratepaid investigation.
__________________
"Facebook is a toilet."  LWTwJO "45 es un titere"  Seal of The President of The United States of America protest art 
#26




Question: is RAND() random? Meaning, can you create a program that predicts the next number it will create?
__________________
"Facebook is a toilet."  LWTwJO "45 es un titere"  Seal of The President of The United States of America protest art 
#27




Quote:
Ah, here it is: Quote:
For those unfamiliar with the theoretical/practical issues with PRNGs, the Wikipedia article is a good start. PRNGs are used for more than just Monte Carlo modeling [which is generally an attempt to approximate some probability distribution, or derived features of said distribution [e.g., expected value]]... many of the PRNGs used for financial stochastic modeling are considered deficient for cryptographic concerns. As for audits, if you save the number sequence that gives you your results, that should be sufficient. They can run their own tests of the goodness of the stats of your PRNs or your model results as they wish. If you're not drawing on the order of a trillion+ numbers for your modeling, you might not have to worry about the repeat. [As atomic mentions, there are some weird things that happen due to the floating point arithmetic in Excel... fixed point is obviously problematic, but even floating point has issues. I've been thinking of writing something up on floating point computation issues for CompAct, or maybe even one of the more technical actuarial journals. This can end up a real problem.... this is not necessarily getting OT with PRNGs, because some of the things I've come across on Excel discussion boards are weird results from RAND() because of precision issues. If you're using RAND() to feed into an infinite range distribution such as the gaussian, you're going to end up with NaN [not a number] if the PRNG gives you either 0 or 1]. 
#28




Quote:
See e.g., http://www.fourmilab.ch/hotbits/
__________________
Brad Gile, FSA, MAAA Affiliate Member of the CAS Dedicated Retired Actuary Spoiler: Spoiler: 
#29




I may be completely off base here but couldn't you use strange attractors to generate random numbers? I think I recall someone talking about that in college but I could be just making it up... atomic to the rescue?

#30




I'm not sure if it's all that applicable to actuarial practice, but the story of the downfall of Planet Poker is still a great example of pseudorandom number generation gone horribly wrong.
[ETA] For an example of random number generation done right, check out Pokerstars' shuffle algorithm. [ETA2] The Pokerstars page contains cites another interesting article: a research paper on the Intel (True) Random Number Generator
__________________
It's business time... Last edited by Jemaine Clement; 07172009 at 02:14 AM.. 
Tags 
data science, excel, predictive analytics, prngs, pseudorandom numbers, rand, random 
Thread Tools  Search this Thread 
Display Modes  

