Famous Ankles

How long did you think that I would wait until I did a perfectly nerdy thing like post a bit about math or Shakespeare or encryption?  I couldn’t resist any longer.

Over the weekend, I was trying to figure out the answer to an old problem of mine:  how do I create a random normal distribution?  The only statistics package I have is Excel and I was working on a little present value function and decided I really, really needed a random normal distribution.  If you know Excel, you know that the “RAND()” function is pretty much okay (no snide remarks about it be a pseudo-random number generator, please), but it produces an equally distributed value between 0 and 1 that you can multiply by any factor to get a random number in a range of your choosing.  However useful this might be for ordinary sampling, I simply couldn’t find a way to generate a normal distribution (you know, the old “bell curve” sort of thing).  And for my not-to-be-disclosed-task, I needed a normal distribution of rates.

I’m sure many of you reading this has often said:  OF COURSE!  Finally, Famous Ankles will reveal how I can generate a normal distribution and I can sleep easy knowing I have it available.  Of course, others will roll their eyes because this may be old hat to them.

One way of the other, here it is:

=NORMINV(MAX(0.001,MIN(1-0.001,ROUND(RAND(),4))),0,1)*$C$7 + $C$6

Where $C$7 is the standard deviation and $C$6 is the mean. 

There are a few issues in there to tweak depending on your needs, but the “0.001” stuff is to make sure that the value in the parentheses remains between 0 and 1; but never equal to 0 or to 1.  If it equals 0 or 1, it will return an error.  In some cases, you might want to make it 0.000000001 or something, but that’s up to you.  Also, the rounding is simply up to your discretion whether to include it or not.

So, plug it in, put a mean (AKA average) and a standard deviation into the appropriate spots and hit F9 until the cows come home!  More advanced users might want to write a macro to generate thousands of them to test.  I did and it sure looks normal to me.

UPDATE:  I took a look at some other information after posting this and have learned that the “NORMINV” function can be used for a normal distribution creation if you use Windows XP, and even then it can be an iffy proposition.  Well, I use XP and my needs are personal and definitely not industrial strength.  If you have that need, you might want to re-think.  For me, I’m pretty much fine with it.

-H

This entry was posted on August 8, 2007 at 8:11 pm and is filed under Administrative. You can subscribe via RSS 2.0 feed to this post's comments. You can comment below, or link to this permanent URL from your own site.