What Does RANDBETWEEN Do in Excel?
RANDBETWEEN is the excel formula that can generate random numbers between two numbers. This is a volatile function and keeps changing when any changes occur in the worksheet.
- Bottom: What is the bottom number to generate the number?
- Top: What is the top number to generate a number?
Once the bottom & top numbers provided, it will generate numbers between these two numbers. For example, if the bottom number is 10 and the top number is 20, then the RANDBETWEEN formula will generate numbers between 10 & 20. Numbers are positive integers.
Examples to use RANDBETWEEN Formula in Excel
Let’s start with a simple example.
- Open the RANDBETWEEN function in the cell in a worksheet.
- Enter the bottom number as 10 and the top number as 30.
- Close the bracket and hit the enter key to get the random number between 10 and 30.
- We got 20 as the answer, perform some other activity in excel; it will change the number to a different one.
Since it is a volatile function, it keeps changing every time when something happens in the worksheet.
We can generate random numbers in multiple cells at a single shot as well. First, select the range of cells that we need to generate random numbers in excelRandom Numbers In ExcelWe use random numbers in Excel when we wish to generate absolutely random random numbers to randomize our data for a sample evaluation, . In Excel, there are two built-in functions that offer us random numbers in cells: the =RAND() function provides us any value from 0 to 1, and the =RANDBETWEEN() function takes user input for a random number range..
Now open the RANDBETWEEN formula.
Supply bottom number and top number.
Close the bracket but do not simply hit the ENTER key; rather, hit CTRL + ENTER to insert random numbers in the selected cells.
Example #3 – Generate Decimal Numbers
Just to inform you excel RANDBETWEEN formula can insert only positive & negative integer numbers. If any of the bottom and top numbers infraction, then excel automatically converts the number to the nearest integer value.
As we can see in the above image, the bottom number is 5.5, and the top number is 15.5. So, Excel automatically converts the bottom number 5.5 to 6 and 15.5 to 16. So we got the numbers between 6 and 16 in the above image.
As I told, the RANDBETWEEN function can generate only integer numbers, not fraction numbers. In order to generate fraction numbers, we need to multiply the value using the RAND function.
RAND function generates numbers that are greater than 0 and less than 1. The value given by the excel RANDBETWEEN formula will be multiplied by the fraction value given by the RAND function.
Example #4 – Generate Random Alphabets
RANDBETWEEN function is not only used to generate random numbers but also to generate random alphabets as well.
We need to enclose the CHAR function in excelCHAR Function In ExcelThe character function in Excel, also known as the char function, identifies the character based on the number or integer accepted by the computer language. For example, the number for character "A" is 65, so if we use =char(65), we get A. with RANDBETWEEN.
RANDBETWEEN function will return numbers from 65 to 90. CHAR function converts the numbers from 65 to 90 from A to Z.
Number 65 is equal to “A”; number 66 is equal to “B” and so on.
Example #5 – Generate Random Dates
We have seen how the RANDBETWEEN formula can be used to generate random numbers and random alphabets. Now we will see how we can generate random dates.
Step 1: Open the DATE function first.
Step 2: The first argument is YEAR, for this open RANDBETWEEN formula and supply years as 2015, 2018.
Step 3: The next argument in the DATE function is MONTH, for this also open RANDBETWEEN function and supply numbers as 1 to 12.
Step 4: The last parameter of the DATE function in excelDATE Function In ExcelThe date function in excel is a date and time function representing the number provided as arguments in a date and time code. The result displayed is in date format, but the arguments are supplied as integers. is DAY, for this supply numbers from 1 to 31 for excel RANDBETWEEN function.
Now hit Ctrl + Enter, we will get random dates in a selected range of cells.
Things to Remember
- RANDBETWEEN is a volatile function.
- It keeps changes result if any activity is done in the worksheet.
- If the bottom number is more than the top number, we will get #NUM! Error.
- RANDBETWEEN can generate only integer numbers.
- A combination of RANDBETWEEN & RAND function in excel can give fraction numbers.
- Since RANDBETWEEN is a volatile function, it may slow down your workbook when the data increases.
This has been a guide to RANDBETWEEN in Excel. Here we discuss how to use the RANDBETWEEN function in excel along with examples and a downloadable template. You can learn more about excel functions from the following articles –