Random number generators are used for a wide range of applications from computer simulation and cryptography, to simply selecting the winner of a prize draw. There are easy-to-use functions within Excel that can do the job for you.
What are random numbers?
Random numbers occur in a sequence that meets two conditions – values are uniformly distributed over a defined interval, and you can’t predict the next number based on past or present ones. The most common type of random numbers come from single-digital decimal numbers between 0-9. There are lots of different ways to generate them, depending on what you require them for, but a simple one is the Excel random number function.
What is Excel Random Number Function?
Excel’s random number function is a way to generate pseudo-random numbers for a wide variety of purposes. Unlike a true random number generator, it does use mathematical formulas so can be predictable in some way, but there are almost no recorded examples of this happening. So, in many situations, generating a random number with Excel will be more than sufficient for the job. Excel has two functions to do the job depending on the type of number involved – the RAND and RANBETWEEN functions.
Excel Random Number Generator
To produce a random number, you can use the RAND function which is simple and creates a random number between 0 and 1. The function will appear as =RAND(). To use this, you open a workbook and insert the function in the A2 cell (if you have headings in A1), and you will receive a randomly generated number.
If you want more than one random number, you can drag the cell down with the fill handle to create a series of random numbers, as many as you might need.
One thing to watch is that if you type something in another cell, it will change the results. So, if you want the numbers to stay the same, highlight the cells with the random numbers, add the function and then CTRL+ Enter. Another option is when you calculate the random number, press F9 rather than enter and this will calculate the formula, storing the result as a value.
Finally, if you want to, you can copy them and paste the values into a new set of cells, so they don’t alter.
Excel Random Number Between
The RAND function is perfect if you need a number between 0 and 1, but it isn’t the only way to generate those random numbers. If you want a random number greater than 1, then you can use the RANDBETWEEN function.
To create this, use the function =RANDBETWEEN(bottom, top) where the bottom is the smallest number you want to use, and the top is the largest. Say you want a number between 1 and ten then the function would be =RANDBETWEEN(1, 10). Remember in this situation, both 1 and 10 can appear in the possible numbers generated.
Like the RAND function, the number will change every time you type something new on the sheet. So you can use the same systems as above to stop this from happening.
If you want to generate non-whole numbers with RANDBETWEEN, then you can use RAND in one column and RANDBETWEEN in the next. Then use the SUM function to add the two together to get a random number with decimals.
Examples of random number use
As mentioned, there are many uses for random numbers but here are a few to show how Excel’s function can be used:
- Random date between two dates
- A random number from a fixed set of options
- A random number from a list or a table
- A random number between two specific numbers
- Random text values
- Assign data to groups on a random basis
For simple, easy to record random number generating, Excel does a great job. It allows you to generate numbers between 0-1 as well as larger numbers for whatever purpose you have. These can be used for anything from selecting a prize winner, in a social media competition, to choosing a random date for an event and even for complex cryptographic purposes and computer programming.