What Does RANDBETWEEN Do in Excel?
RANDBETWEEN is the excel formula which 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.
How to use the RANDBETWEEN Formula in Excel? (with Examples)
Let’s start with a simple example. Open RANDBETWEEN function in of the cell in a worksheet.
Enter the bottom number as 10 and top number as 30.
Close the bracket and hit 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.
Now open RANDBETWEEN formula.
Supply bottom number and top number.
Close the bracket but do not simply hit 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 the any of the bottom and top numbers is fraction 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 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 CHAR function 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 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 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 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 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 RANDBETWEEN function in excel along with the examples and downloadable excel sheet. You can learn more about excel functions from the following articles –