About the RAND() function in Excel
By Michael Barden
Typing the =rand() function into a cell in an Excel spreadsheet will return a random number that is greater than or equal to 0 and strictly less than 1, with up to 9 numbers after the decimal place (e.g. 0.037595172 or 0.999999999).
If you then press F9, a new random number will take the place of the old one. This is because F9 re-calculates the formulas on all open worksheets based on the current data, including all random numbers.
Ok… We Really Mean “Pseudo-Random” Numbers
The =rand() function in Excel cannot be easily analyzed to produce a predictable pattern, but it is not a truly random process. To be perfectly correct, the numbers generated are in fact approximations of actual random numbers known as “pseudo-random” numbers.
Computers are by definition “deterministic” – when given a particular input, they will always produce the same correct output. This is an inherent problem when it comes to producing a purely random output, and so algorithms have been developed to simulate random behavior.
For people who don’t rely on a heavy use of random numbers, the =rand() function in Excel 2003 should be more than enough.
In some heavy-use situations however, it won’t. If you are serious about your need for a very good random number generator, then you may want to look at a number of third-party alternatives.
Note that the =rand() function does not try to avoid repeating a number.
With that said, Excel 2003 has an upgraded random number generator over the previous versions of Excel (whose random number generators were simply not sufficiently random).
Naturally, this change introduced a bug that allowed for negative random numbers! We will talk about this bug below.
Building on The rand() function
So far we know about random numbers between 0 and 1, but in many cases we want larger random numbers. If you want to increase the range to find numbers between 0 and 50, use the =rand()*50 formula (where the * character represents the multiplication sign). This acts to “scale” the random numbers between a range of 0 and 49.999999999.
What if you want a random number in a certain range – say between 20 and 70? To do this, =rand()*(y-x)+x where x is the smallest number in the range and y is the largest number in the range. In our example, x=20 and y=70.
When you plug in the values to our formula it becomes =rand()*(70-20)+20, which in turn becomes =rand()*(50) + 20. We know from above that =rand()*50 will produce a random number between 0 and 50. If we then add 20 to the result, all we are doing is “shifting” the range of possible numbers by 20. Hence, it produces a random number between 20 and 70 (or 20 and 69.999999999 if you want to be more precise).
Whole Number only
If you only want whole numbers (integers), then you can combine the rand() and int() functions to obtain only the integer part of the random number. For example, typing in =int(rand()*100) would generate a random integer between 0 and 99 inclusive.
Similarly if you wanted whole numbers between 1 and 100 inclusive, then simply shift the range by adding 1 as follows: =int(rand()*100) + 1.
To emulate the roll of a dice use =int(rand()*6) + 1
If you want some assurance that your formula is working correctly, copy and paste it into a number of cells and press F9 a number of times to observe that the results are always within the desired range.
Mathematics I hear you say? I don’t want to have to know mathematics! After all, it’s easy to make a mistake trying to scale and shift your random numbers.
Luckily there is an easier way of creating a random whole-number within a certain range in Excel 2003. This is done by using the =randbetween(bottom,top) function where “bottom” is the smallest integer the function will return, and “top” is the largest integer the function will return. For example, =randbetween(1,100) will produce random integers between 1 and 100 inclusive.
Similarly if you wanted a range of positive and negative values, you could use something like =randbetween(-100,100).
Note: unlike RAND() – RANDBETWEEN() returns integer values only with no fraction. Some documentation is unclear on this point, talking about ‘numbers’ instead of ‘integers’
If you want a random number with fractions in a certain range you need to use RAND() in a formula.
The #Name Error and Fix
If you try to test the =randbetween(bottom,top) function in Excel 2003 and it is not available, it will return a #NAME? error.
To fix this you need to install and load the “Analysis ToolPak” add-in, by first navigating to “Tools | Add-Ins” and then selecting the “Analysis ToolPak” box from the list provided. Finally clicking “OK” to install the add-in.
In Excel 2007 this somewhat pointless distinction between functions is removed – any functions from the old ‘Analysis Toolpak’ are included in Excel 2007 by default.
Negative Random Numbers in Excel
As mentioned above, Microsoft’s attempt to improve the =rand() function for Excel 2003 was originally flawed as it unwittingly introduced occasional negative random numbers.
Office Watch broke the news of the Excel 2003 RAND() bug at the time.
I love this quote from that issue:
“The company says that ‘The new generator passes all standard tests of randomness.’ which is great but a pity it wasn’t tested for plain and simple accuracy.”
This problem was fixed in the Microsoft Excel 2003 Hotfix Package over 2 years ago, so unless you are running an un-patched version of Excel 2003, you can be assured that your random numbers will always be positive.
A simple way to test this is to type the =rand() function into one cell, then copy and paste it into a number of other cells until you have a large amount of random numbers. Pressing F9 will “recalculate” all of these random numbers. If you have 50 or so random numbers and you press F9 up to 20 times (quickly checking each time for any negative results), you can easily put your mind at rest.
The only way to resolve the negative random number problem is to obtain the latest service pack for Microsoft Office 2003 using either Microsoft Update or Office Update. This is generally a good idea anyway!
- Big numbers rounded in Excel
- New Prime Number is too big for Excel
- Benford’s Law and Excel
- El Patron de los Numeros Primos
- Sabotaging Random Numbers
- Getting truly random numbers into Excel
- Random Numbers