Using Excel Random Numbers in Uniform Distributions

Excel can be used to return pseudo random numbers using the RAND function. This function has no arguments, and simple typing “=RAND()” into a cell will generate a figure in that cell.

RAND returns values that fall in between 0.000000000000000 and 0.999999999999999 (fifteen digits):

Random numbers through RAND

There are some quirks with this function. Most worksheets are set to autocalculate. If this is the case, any time the sheet is changed (data is entered or deleted, etc) the RAND function will run again. This means every time you change something, a new set of random numbers will appear. In practice, working with excels random number function can be very difficult as changes to the model will change all of the random numbers. Below you can see how each number has changed after I inserted a column – little changes like this can completely alter your work.

We can get around this issue by turning off autocalculate. Then when we want to recalculate, we use F9 to manually calculate the cells. This will limit some of the inherent volatility of models produced using RAND somewhere in them. Additionally, there is no way to seed RAND, so you cannot recreate a set of random numbers a second time around.

I went ahead and plotted our distribution after increasing the sample size and calculating the frequencies using intervals of .05, click here for guide to creating bins and plotting frequency distributions from a given dataset. As you can see, our data is uniformly distributed:

A randomly generated, uniform distribution.

If we were to increase the number of observations, we would see the traits of a uniform distribution much more clearly but you can still make out that there appears to be a roughly even distribution of numbers from zero to 1.

A final potential limitation is that RAND generates numbers that form a uniform distribution rather than a normal distribution. This can we worked around by placing RAND inside of either Norm.S.Inv or Norm.Inv depending on whether one wants a standard normal distribution a normal distribution. See a more thorough discussion here.

Your email address will not be published. Required fields are marked *