You had to know it was coming: a math/statistics post

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

Advertisements
Explore posts in the same categories: Administrative

One Comment on “You had to know it was coming: a math/statistics post”

  1. Mr Farty Says:

    1. You sad, sad git.

    Now we’ve got that out of the way…

    2. An ex-work colleague who wrote some rather dire computer code used a random number function to generate his error messages. I can’t for the life of me imagine why he was fired.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: