Excel Random Numbers
Quickly & Easily Generate and Use Random Numbers
By Scott Hartshorn
Thank You!
Thank you for getting this book! This book shows how to use the Excel random number functions to generate pseudo-random numbers. These functions are very useful for simulations. Fortunately, the random number functions are also very easy to use !
If you want to help us produce more material like this, then please leave a positive review for this book on Amazon. It really does make a difference!
Your Free Gift
As a way of saying thank you for your purchase, I’m offering this free Excel tips and tricks book that’s exclusive to my readers.
This book contains my favorite Excel functions and settings that I have seen in years of using Excel, searching for tips, and asking people “How did you do that?”. You can download it by going here
http://www.fairlynerdy.com/excel_tips_and_settings/
Random numbers have a lot of useful and fun properties. Not the least of which is that if you run into a problem that is too hard for a closed form solution, simulating it with random numbers is almost always easier. Excel has a fairly easy to use pseudo-random number generator. Coupled with Excel’s other good functions, it is very useful for generating a moderate quantity of random numbers for small to medium simulations. This book walks you through how to generate random numbers in Excel, and some variations you can use them for such as sorting, or making a normal distribution. If you want to
duplicate some of these examples, you can get Excel workbook I used at http://www.fairlynerdy.com/excel_random_numbers
Table of Contents
Your Free Gift
Getting Started With Random Numbers
Getting Random Numbers in a Different Range
Getting A Certain Number Of Decimals
Stable Random Numbers
Sorting With Random Numbers
Making A Normal Distribution
Using The Data Analysis Tool Pack
How Good Are The Random Numbers In Excel?
Other Books
Thank You
Getting Started With Random Numbers
There are two Excel functions you can use to generate random numbers. The essentially work the same way, except one function generates integers, and the other generates real numbers (i.e. numbers with a decimal). The two functions that will generate random numbers are
=RAND() and
=RANDBETWEEN()
=RAND() will generate a random number between 0 and 1.
The RANDBETWEEN() takes two inputs, a bottom and a top, and returns an integer between those two values
Of course, if you want multiple random numbers, you can just repeat the RAND() function in multiple cells
Getting Random Numbers in a Different Range
Let’s say that you want a random number with decimals, but you don’t want it to fall in the 0-1 range, you want it in a different range. If you wanted to use integers, it would be obvious how to use RANDBETWEEN(Top, Bottom) to put the answer in another range. But with decimals, you need to do something slightly different.
To get a random decimal in an arbitrary range, you need to start with the RAND() function, then multiply by the size of your desired range, and add the bottom number of the range. For instance, if you wanted a random number between 200 and 300, the size of the range is 100 (i.e. 300-200), and the bottom number is 200. So you can use the equation =RAND()*100 + 200 to get a random number between 200 and 300
If you wanted values between -100 and 100, you could use the function
= RAND()*200 – 100.
Basically by multiplying by a constant, and adding a constant you can get values in any range you want
Getting A Certain Number Of Decimals
If you only want a certain number of decimals, let’s say you are looking for a 2 decimal random number between 50 and 100, you can use the round() function on the random number. That equation would be
= Round( Rand()*50 + 50, 2)
Stable Random Numbers
One thing to know about random numbers is that they are an unstable function. That means they will update every time Excel does a calculation, basically every time you press Enter or Delete the random numbers will change.
For instance, if you had a column of random values and were trying to make a histogram of the frequencies using the =Countif() function
You might make the histogram, draw some conclusions about it, and then update something else in the Excel workbook and the answers will change.
This is the count of the random numbers shown above
But simply hitting the delete key on a blank cell will recalculate the random numbers and get different answers. Since there is no random seed, there is no way to get the original answers back
These unstable values may not matter to you. It might be OK for the answers to update for the function you are using it for. However if you want to get random numbers that don’t change there are a few good ways to do it.
Option 1 – Generate Random Numbers, Then Copy the Values
One way to get stable random numbers is to generate the random numbers one time, and then copy the values to a new location using the Paste Special – values function. The new copied values will be random, and they will not change as the original random numbers update.
Right clicking will bring up the screen where you can access paste special
The result in that column will be the random numbers that were in the random column when you pasted. (which will then change)
Option 2 – Use F9 To Delete The Random Formula
The second way to get a random value, is to use the random function, and then blow away the formula, keeping only the value. If you are in a cell, pressing F9 will replace the formula of the cell with the current value
After pressing F9 in that cell, the new value in the cell isn’t the formula, it is 155, which was the current random value
Option 3 – Use The Calculation Options
The other way to get stable random numbers works really well. It is to set the calculation options to Manual so that Excel will only update the calculations on the spreadsheet when you tell it to. It will not update every cell entry or deletion.
The calculation options is on the Formula ribbon, in the calculation tab
The calculation options defaults to automatic, but you can set it to manual calculation
The Excel cells will only recalculation when you use the “Calculate Now” or “Calculate Sheet” options.
The calculate now button updates the whole Excel file, the calculate sheet only updates the worksheet that is currently active. So one thing that you can do is put the Random numbers into their own worksheet, and then use the calculate sheet any other worksheet.
The unfortunate thing about this option is that Excel automatically recalculates the random numbers when you save the file, even if calculation options are set to manual
Sorting With Random Numbers
One useful thing to do with random numbers is sort items. Let’s say that you have a list, which is this case is a list of the most popular first names in the United States in the 1990’s, and you need to sort the list randomly. This sorting could be to select the order for people for something, it could be to
randomly assign items into groups, or anything that you need a random list for.
You start by making a row of the list, and a row of random numbers beside it, using the RAND() function
You can then use the data filters to sort the random numbers in ascending or descending order
And you end up with a randomly selected order of the items
This sorting technique is useful anytime you have to generate a subset of a list without replacement.
That might be selecting a which students have to give their presentations the first day, or selecting random non-repeating numbers from a finite list. (i.e. for something like a lottery, except don’t use Excel random numbers for any lotteries with serious amounts of money)
Making A Normal Distribution
The Random numbers in Excel come out with an even probability distribution. That means that every number is as likely as any other number to be selected. But there are many applications where you do not want an even probability distribution, many functions need a normal probability distribution. You can use the RAND() function, combined with the norm function to generate a normal distribution.
The function that you need within Norm is NORM.INV(). For the Norm.INV() function you input a probability between 0 and 1, a mean value and a standard deviation. So to get a random value from the normal distribution, mean a mean of 0 and a standard deviation of 2 you would use
= NORM.INV( RAND(), 0, 2)
In this worksheet, I calculate the RAND() value in an adjacent cell
I did this for 10,000 cells and made a histogram of the count of the results, and end up with an approximate normal curve
There are still bumps, because even 10,000 numbers isn’t a large enough sample size to make the normal curve completely smooth.
Using The Data Analysis Tool Pack
In addition to the RAND() and RANDBETWEEN() functions, there is another way of generating random numbers in Excel. That is to use the Data Analysis Tool Pack. This can be found in the analysis tab of the Data Ribbon
If that add in isn’t visible for you, this page will show you how to turn it on http://www.excel- easy.com/data-analysis/analysis-toolpak.html
When you click on the data analysis tool pack you can select random number generation
Which brings up this form
The number of variables entry is the number of columns of random numbers, the number of random numbers entry is the number per column. Entering 2 for the number of Variables and 10 for Number of Random Numbers will yield 20 random numbers. You can put a value in for the Random Seed, and if you use that value again, the same random numbers will be generated.
These random numbers are different from the RAND() generated random numbers. They will not change when you press enter or delete.
You have a few options for distribution when you generate the random numbers
Uniform distribution – an even distribution between the starting and ending point, just like RAND()
Normal Distribution – Random numbers in the normal curve, enter a mean value and a standard deviation
Bernoulli, Poisson distributions – Random numbers for the Bernoulli or Poisson distributions
Binomial – Enter a probability and a number of trials to get a random number following the binomial distribution. For instance, if you want to simulate flipping a coin 20 times, enter .5 for probability and 20 for number of trials
Patterned – These aren’t really random numbers, this is just making a pattern of numbers, for instance 1, 1, 3, 3, 5, 5, 7, 7
Discrete – Input values and the probability you want those values to occur at. For instance if you want a 50% chance of getting a 1, a 30% chance of getting a 2, and a 20% chance of getting a 3 you can input this range
And get output at that probability
The data analysis tool pack can be very useful for generating random numbers, and gives you options that are hard to duplicate using the RAND() or RANDBETWEEN() formulas. The biggest downside to using the tool pack is that the algorithm used to generate the random numbers is not as good and the algorithm used in the formulas.
How Good Are The Random Numbers In Excel?
Since you know how to use the random number generator in Excel, the question comes up, how good is it? In terms of the quality of the random number generator itself, the RAND() function in Excel 2003 and Excel 2007 is not very good. The RAND() function in Excel 2010 is substantially better.
Like any random number generator on a computer, Excel is not generating true random numbers.
Rather it is generating pseudo-random numbers. Which means that is it generating the numbers using a mathematical formula. Pseudo-random number generators are fine for many purposes. You
wouldn’t want to use them for cryptography, or anywhere where large amounts of money were on the line like a lottery, but they can still be useful. The problem with RAND() in Excel 2003 and 2007 is the pseudo-random numbers are not implemented all that well.
This paper by B.D. McCullough, “Microsoft Excel’s ‘Not the Wichmann-Hill’ random number generators”
Puts it succinctly
“Microsoft attempted to implement the Wichmann-Hill RNG in Excel 2003 and it failed; …. Microsoft issued a patch that allegedly fixed the problem so that the patched Excel 2003 and Excel 2007 now implement the Wichmann-Hill RNG, at least according to Microsoft. We show that whatever RNG it is that Microsoft has implemented in these versions of Excel, it is not the Wichmann-Hill RNG. Microsoft has now failed twice to implement the dozen lines of code that define the Wichmann-Hill RNG”
This paper by Guy Melard “On The Accuracy of statistical procedures in Microsoft Excel 2010” ran similar randomness tests on the RAND() in Excel 2010 and found that Microsoft had changed their random algorithm and it was substantially improved.
Excel 2003 & 2007 RAND() functions are probably usable for generating thousands or tens of thousands of random numbers, but not millions of them. Excel 2010 and later should be usable for millions of numbers.
Unfortunately, none of the versions allow you to set a starting seed for the formula based random numbers, so the random numbers are not reproducible.
It should be noted that the RAND() or RANDBETWEEN() functions in Excel are only one of the ways to generate random numbers in Excel. Additionally the user could use the VBA RNG function, or the random number generator in the Data Analysis tool-pack. Unfortunately the quality of both of those random number generators is substantially worse than the quality of the RAND() function, even in Excel 2003.
If you are looking for True random numbers, not generated with a random number generator, then the website https://www.random.org/ allows you to download a moderate quantity of random numbers for free ( 200,000 bits of randomness per day, which is enough for 33,333 random numbers between 0 and 100) or purchase a larger quantity of random numbers.
Other Books
If you found this book useful, you may like some of other books, all of which focus on straightforward explanations and examples.
Excel Data Filters - How to use the data filters in Excel to sort and organize data
Hypothesis Testing Examples – Half a dozen worked examples in Excel on how to calculate statistical significance
Bayes Theorem Examples – Examples worked in Excel on how you can use Bayes theorem to update an estimated probability based on new informationThank You
Before you go, I’d like to say thank you for purchasing my eBook. I know you have a lot of options online to learn this kind of information. So a big thank you for downloading this book and reading all the way to the end.
If you like this book, then I need your help. Please take a moment to leave a review for this book on Amazon. It really does make a difference, and will help me continue to write quality eBooks on Math, Statistics, and Computer Science.
If you want to keep up to date on any new eBooks, examples, or cheat sheets you can find us on Facebook at
https://www.facebook.com/FairlyNerdy or interact with us at our home page http://www.FairlyNerdy.com