Part of our Statistical Functions in Excel guide
What Is RANDBETWEEN Function In Excel?
The RANDBETWEEN is the Excel formula that can generate random numbers between two. It is a volatile function and keeps changing when changes occur in the worksheet.
For example, when we type =RANDBETWEEN(30,40), it generates 33.
In this article, let us learn the different scenarios where we can use RANDBETWEEN with apt examples.
Key Takeaways
- RANDBETWEEN function in Excel, in simple terms, generates random numbers between two values. Remember, the changes to the worksheet affect the formula’s result.
- Similarly, if the bottom number is higher than the top, the formula shows a #NUM! Error.
- The RANDBETWEEN function in Excel can only produce integers.
- RANDBETWEEN and RAND functions in Excel can generate random numbers, but using RANDBETWEEN for decimals can slow down the workbook.
- RANDBETWEEN generates only integers, whereas RAND can generate decimals. We need to pick the proper function, as both can slow down performance with large data sets.
RANDBETWEEN() Excel Formula
- Bottom: What is the bottom number to generate the number?
- Top: What is the top number to create a number?
Once the bottom and top numbers are provided, they will generate numbers between them. For example, if the bottom number is 10 and the top number is 20, then the RANDBETWEEN formula will generate numbers between 10 and 20. That is because the numbers are positive integers.
How To Use RANDBETWEEN Excel Function?
The RANDBETWEEN function in Excel is not only used to generate numbers but also to generate many more. In this article, let us learn the uses of RANDBETWEEN with the following detailed examples.
Examples
Example #1
Let us start with a simple example.
1. To begin with, open workbook and enter the RANDBETWEEN function in the cell in a worksheet.
2. Next, insert the bottom number as 10 and the top number as 30.
3. Then, close the bracket and press the “Enter” key to get the random number between 10 and 30.
4. Now, we got 20 as the answer and performed some other activities in Excel. So, it will change the number to a different one.
Since it is a volatile function, it keeps changing whenever something happens in the worksheet.
Example #2
Meanwhile, we can also generate random numbers in multiple cells at a single shot. To start, first, select the range of cells we need to create random numbers in excel.
Now, open the RANDBETWEEN formula.
Next, supply bottom number and top number.
Now, close the bracket but do not press the “Enter” key. Rather, press the “CTRL + ENTER” keys to insert random numbers in the selected cells.
Example #3 – Generate Decimal Numbers
For information, the Excel RANDBETWEEN formula can insert only positive and negative integer numbers. If any of the bottom and top numbers are fractions, 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 we said, the RANDBETWEEN function can generate only integer numbers, not fraction numbers. Therefore, we must multiply the value using the RAND function to generate fraction numbers.
The RAND function generates numbers greater than 0 and less than 1. Therefore, the value given by the Excel RANDBETWEEN formula will be multiplied by the fraction value given by the RAND function.
Example #4 – Generate Random Dates
We have seen how we can use the RANDBETWEEN formula to generate random numbers and alphabets. Now, we will see how we can develop random dates.
Step 1: To start with, we must open the DATE function first.
Step 2: Note that the first argument is YEAR. For this, open the RANDBETWEEN formula and supply years like 2015 and 2018.
Step 3: Next, the second argument in the DATE function is MONTH. For this, open the RANDBETWEEN function and supply numbers 1 to 12.
Step 4: Finally, the last parameter of the DATE function in excel is DAY. For this supply, numbers from 1 to 31 for the Excel RANDBETWEEN function.
Now, press the “Ctrl + Enter” keys. Again, we will get random dates in a selected range of cells.
Important Things To Note
- The RANDBETWEEN Excel function is volatile.
- It keeps changing the result if any activity is done on the worksheet.
- If the bottom number exceeds the top, we will get #NUM! Error.
- The RANDBETWEEN function in Excel can generate only integer numbers.
- A combination of RANDBETWEEN and RAND function in excel can give fraction numbers.
- As the RANDBETWEEN is a volatile function, it may slow down the workbook when the data increases.
Frequently Asked Questions (FAQs)
1. What is the RANDBETWEEN formula for text?
To generate a random number within a specified range, we can use the RANDBETWEEN function. The syntax for this function is =RANDBETWEEN(bottom, top), where the ‘bottom’ value represents the lowest number in the range and ‘top’ represents the highest number in the range.
Similarly, we can generate RANDBETWEEN for alphabets.
We need to enclose the CHAR function in excel with RANDBETWEEN.
The RANDBETWEEN function will return numbers from 65 to 90. The CHAR function converts the numbers from 65 to 90 from A to Z.
The number 65 is equal to “A”; the number 66 is equivalent to “B,” and so on.
Therefore, it is evident that the RANDBETWEEN function is used not only to generate random numbers and text, but also to generate random alphabets.
2. How do I get the RANDBETWEEN function to stop changing?
To prevent an Excel random function from recalculating, you can use the ‘Paste Special > Values’ feature. To do this, first, select all the cells containing the random formula. Next, press ‘Ctrl + C’ to copy them. Then, right-click the selected range and click ‘Paste Special > Values’. This function will convert the formulas to their current values, and they will no longer recalculate automatically
3. Is RANDBETWEEN inclusive in Excel?
The RANDBETWEEN function generates a random integer between two given values, including the given values themselves. The random integer generated is equally likely to be any number within the range.
Recommended Articles
This article is a guide to RANDBETWEEN in Excel. Here, we discuss using the RANDBETWEEN function in Excel, examples, and a downloadable template. You can learn more about Excel functions from the following articles: –