Use RANDBETWEEN function to create random number between two numbers (bottom and top).
Whenever we work on worksheet it calculates for new number every time. After completing calculation copy the list of random numbers and use Paste Special > Values. By using RANDBETWEEN function we can create a random list of numbers, text, date etc.
=RANDBETWEEN (bottom, top)
- bottom – In this argument we mention our bottom number
- top – In this argument we specify top number for our list
Useful Additional Notes:-
Examples – RANDBETWEEN Function
We will learn 4 different type of examples: –
- Return 10 random numbers between the numbers 1 to 20
- Return 10 random dates in between 1-Jan-2016 to 31st-Dec-2016
- Create a list for the fixed set of numbers (10,20,30,40,50)
- Create a list of Random Text Values
1st Example: – List of Random Numbers
- Enter function in cell A12, =RANDBETWEEN(1,20)
- Function will return any random number in between 1 to 20
- Copy formula in the range A12:A21
2nd Example: – List of Random Dates
To create the list of random dates we will use DATE function along with RANDBETWEEN function. Follow the steps given below: –
- Enter function in cell B12, =RANDBETWEEN(DATE(2016,1,1),DATE(2016,12,31))
- Function will return random date between 1st Jan’16 to 31st Dec’16
- Copy same formula in the range B13:B21
Formula Explanation: – In this function we have used DATE function to get the date and DATE function is used at the place of both arguments bottom and top. RANDBETWEEN function will help to get the random numbers from the specified date range.
3rd Example: – List for Fixed Set of Numbers (10,20,30,40,50)
In this example, we will use CHOOSE function along with RANDBETWEEN function to create the list from the set of numbers, follow the steps given below: –
- Enter function in cell C12, =CHOOSE(RANDBETWEEN(1,5),10,20,30,40,50)
- Function will return any number from the list (10,20,30,40,50)
- Copy same formula in range C12:C21
Formula Explanation: – CHOOSE function we have used to choose the number from the mentioned numbers in formula and RANDBETWEEN function will help to get the number randomly from the specified list of numbers
4th Example: – List of Random Text Values
To create the list of random text values, we will use CHOOSE function along with RANDBETWEEN function.
- Enter function in cell D12, =CHOOSE(RANDBETWEEN(1,2),”Qualify”, “Unqualify”)
- Function will pick and return text value from the specified text value in formula
- To get the result, copy same formula in range D12:D21
These are the few different ways to use of RANDBETWEEN function in Microsoft Excel.
RAND | CHOOSE