Actuarial Outpost Pseudorandom number generation in Excel
 User Name Remember Me? Password
 Register Blogs Wiki FAQ Calendar Search Today's Posts Mark Forums Read
 FlashChat Actuarial Discussion Preliminary Exams CAS/SOA Exams Cyberchat Around the World Suggestions

 Enter your email to subscribe to DW Simpson weekly actuarial job updates. li.signup { display: block; text-align: center; text-size: .8; padding: 0px; margin: 8px; float: left; } Entry Level Casualty Health Life Pension All Jobs

 Thread Tools Search this Thread Display Modes
#1
07-16-2009, 02:09 PM
 campbell Mary Pat Campbell SOA AAA Join Date: Nov 2003 Location: NY Studying for duolingo and coursera Favorite beer: Murphy's Irish Stout Posts: 82,421 Blog Entries: 6
Pseudorandom number generation in Excel

I was asked as to why RAND() in Excel sucks in a different thread.

I believe this is an important enough topic I'm spinning it off to here.

I'm going to give two practical reasons up front - and this holds for all versions of Excel that have a RAND() function. I can talk about tests of randomness at a later time. The first issue is relatively minor, if annoying, but the second issue is a big bold X over using the function for serious Monte Carlo modeling.

1. RAND() is volatile

That sucker reevaluates every time the spreadsheet recalculates, which could be every time you change a cell if you have it on automatic calculation. If you have too many RAND() calls, your spreadsheet will be slower than molasses even if you just type a short string in a cell.

A bit of info on volatile functions:
http://www.decisionmodels.com/calcsecretsi.htm

You could keep your spreadsheet in manual calculation mode, but that has dangers of its own.

2. RAND() is an unseeded PRNG

This is important for serious Monte Carlo applications. You need to be able to reproduce a particular sequence of numbers. The way this is accomplished in most PRNGs is by having a seed for the PRNG, which is an argument passed to the generator. This will set the initial "random" number for a sequence, and then you will get a specific "random" sequence out for however long it takes until it repeats.

One of the real reasons you need this seed is so you can put the PRNG through a bunch of tests - the Diehard suite of tests is common, but there are other tests you can get from the National Institute of Standards and Tech [NIST].

When you combine 1 & 2, you basically get an untestable algorithm, and I really can't rely on Microsoft statements such as this one:
http://support.microsoft.com/kb/828795

They claim that the current [Excel 2003 & 2007] RAND() has a period of >10^13 [so it will take over 10 trillion calls before it repeats] and that the underlying PRNG satisfies the Diehard tests.

But how can I test that? Sure, I can get around the volatility issue by setting calculation to manual, but then I have the issue of generating over 10 trillion numbers to try to catch the repeat... but because of the structure of Excel, I'm not quite sure what the "order" of these numbers is supposed to be. With seeded PRNGs, you can say "I want the 1000th number generated in the sequences seeded with 2362357" [or whatever]. I can't do that with Excel's RAND().

Of course, I can get around the reproducibility problem by saving all the numbers generated in my pseudorandom sequence... but why should I have to when there are perfectly decent seeded PRNGs out there?

More later.
__________________
It's STUMP

#2
07-16-2009, 02:19 PM
 tommie frazier Member Join Date: Aug 2003 Favorite beer: The kind with 2 e's Posts: 22,678

thanks campbell. I have always heard that it was lousy for serious simulations, but hadn't cared to dig into the why of it.
#3
07-16-2009, 02:30 PM
 ADoubleDot Member SOA Join Date: Nov 2007 Location: Slightly Dusty South Studying for the rest of my life Posts: 37,127

Thanks, that makes sense. The volatility thing is truly annoying, but I guess excel will work for what I need. I just wanted to make sure it wasn't wrong.
__________________
ADoubleDot: I'm an actuarial icon.

**** Juan.
#4
07-16-2009, 03:03 PM
 1695814 Member SOA AAA Join Date: Aug 2002 Studying for GED Favorite beer: Root Posts: 34,496

Quote:
 Originally Posted by campbell This is important for serious Monte Carlo applications. You need to be able to reproduce a particular sequence of numbers.
why is that?

because of this?
Quote:
 Originally Posted by campbell One of the real reasons you need this seed is so you can put the PRNG through a bunch of tests.
and why is that? to show that it's random-enough?
__________________
Sheep Dog Dare Game Champion

come...play FSP
#5
07-16-2009, 03:05 PM
 JMO Carol Marler Non-Actuary Join Date: Sep 2001 Location: Back home again in Indiana Studying for Nothing actuarial. Posts: 37,188

Quote:
 Originally Posted by 1695814 why is that?

For your auditors who want you to document your MonteCarlo process. For doing reruns of a stochastic model with corrected data. For coming up with a winning strategy at roulette.

Quote:
 because of this?and why is that? to show that it's random-enough?
yes, exactly.

And, when I first started doing actuarial work, PCs had not been invented.

So we had a statistics book that included a table of random numbers. It was used to sample our activities at various times so that we wouldn't have to keep a detailed time log. This data was then used in expense allocations.

It was also useful for selecting samples, such as for selecting data for the auditors. Really.
__________________
Carol Marler, "Just My Opinion"

Pluto is no longer a planet and I am no longer an actuary. Please take my opinions as non-actuarial.

My latest favorite quotes, updated Apr 5, 2018.

Spoiler:
I should keep these four permanently.
Quote:
 Originally Posted by rekrap JMO is right
Quote:
 Originally Posted by campbell I agree with JMO.
Quote:
 Originally Posted by Westley And def agree w/ JMO.
Quote:
 Originally Posted by MG This. And everything else JMO wrote.
And this all purpose permanent quote:
Quote:
 Originally Posted by Dr T Non-Fan Yup, it is always someone else's fault.
MORE:
All purpose response for careers forum:
Quote:
 Originally Posted by DoctorNo Depends upon the employer and the situation.
Quote:
 Originally Posted by Sredni Vashtar I feel like ERM is 90% buzzwords, and that the underlying agenda is to make sure at least one of your Corporate Officers is not dumb.

Last edited by JMO; 07-16-2009 at 03:12 PM.. Reason: Adding stuff from the olden days
#6
07-16-2009, 03:06 PM
 Bubba Colbert Member Join Date: Mar 2008 Posts: 1,918

Pseudolus is one of a kind, I don't think you can randomly generate more of him. Although Mrs. Pseudolus has the best shot at coming close.
#7
07-16-2009, 03:43 PM
 DeepPurple Member Join Date: Jun 2004 Posts: 4,022

I think that you can seed the Excel PRNG in VB.
__________________
Come on. Let's go space truckin'. Come on!
#8
07-16-2009, 03:47 PM
 E Eddie Smith SOA AAA Join Date: May 2003 College: UGA Posts: 8,999

Quote:
 Originally Posted by Bubba Colbert Pseudolus is one of a kind, I don't think you can randomly generate more of him. Although Mrs. Pseudolus has the best shot at coming close.
Now I think you're alluding to the RANDY() function.

Spoiler:

sarcasm <> hijacking

__________________

Learn how FSA exams are different from the prelims

We have your exam covered:

LP | LFV-U | LFV-C | LRM | ERM | QFI Core | QFI Adv | QFI IRM | G&H Core | G&H Adv | G&H Sp

Check out our Technical Skills Course and our new R Course!

#9
07-16-2009, 04:09 PM
 echo Member Join Date: Nov 2002 Posts: 848

Quote:
 Originally Posted by campbell 1. RAND() is volatile That sucker reevaluates every time the spreadsheet recalculates, which could be every time you change a cell if you have it on automatic calculation.
This is Microsoft's solution. From the help manual under RAND():

"If you want to use RAND to generate a random number but don't want the numbers to change every time the cell is calculated, you can enter =RAND() in the formula bar, and then press F9 to change the formula to a random number. "

What a wonderful solution! What could be better? Interesting that they don't even mention copy/ paste special(values) or as you suggested setting the sheet to manual calculation.
#10
07-16-2009, 04:20 PM
 spencerhs5 Member Join Date: Jan 2007 Posts: 1,475

If doing simulation wouldnt you be likely to be using VBAs rnd function? Which if I recall has limitations as well but can be initialized so that its truely random. I could be wrong, but I think this is what I remember.

 Tags data science, excel, predictive analytics, prngs, pseudorandom numbers, rand, random

 Thread Tools Search this Thread Search this Thread: Advanced Search Display Modes Linear Mode

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is On HTML code is Off

All times are GMT -4. The time now is 07:54 PM.

 -- Default Style - Fluid Width ---- Default Style - Fixed Width ---- Old Default Style ---- Easy on the eyes ---- Smooth Darkness ---- Chestnut ---- Apple-ish Style ---- If Apples were blue ---- If Apples were green ---- If Apples were purple ---- Halloween 2007 ---- B&W ---- Halloween ---- AO Christmas Theme ---- Turkey Day Theme ---- AO 2007 beta ---- 4th Of July Contact Us - Actuarial Outpost - Archive - Privacy Statement - Top