Excel has had the RAND function for a long time. In Excel 2007 a new function was added. Called RANDBETWEEN it made it easier to create random numbers.
RANDBETWEEN is useful when you are testing, or learning, a feature in Excel and you need a series of numbers to play with.
The RAND function returns a random number between zero and one. This means you need to multiply the result by another number to create a larger number. It also means you usually have to use the ROUND function to get whole numbers. This complicates the process of generating random numbers and so the RANDBETWEEN function was added in Excel 2007.
The RANDBETWEEN function allows you to specify a starting value and an ending value. Excel will calculate a number between those two. The result is a whole number, so if you need a series of random two decimal numbers, you will need to divide the result by 100.
The image below has an example of the RAND and the RANDBETWEEN functions.
Note: the random numbers generated may not be unique. The smaller the gap between the lower and upper values in the RANDBETWEEN function, the more likely it will be repeat numbers. As you can see from the example above, many numbers are repeated in column B.
Every time Excel calculates, the random numbers generated will change. In many cases you may want use Copy > Paste Special > Values to capture the random numbers so that they don’t change.
Check out my next blog post to see how you can randomise text entries.
Please note: I reserve the right to delete comments that are offensive or off-topic.