RANDBETWEEN Excel Formula

Article byJeevan A Y
Edited byNannila Jai Ratna
Reviewed byDheeraj Vaidya, CFA, FRM

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

randbetween syntax
  • 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.

RANDBETWEEN Excel Formula

You are free to use this image on your website, templates, etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: RANDBETWEEN Excel Formula (wallstreetmojo.com)

–>> If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

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.

You can download this RANDBETWEEN Function Excel Template here – RANDBETWEEN Function Excel Template

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.


    randbetween example 1.1

  2. Next, insert the bottom number as 10 and the top number as 30.


    randbetween example 1.2

  3. Then, close the bracket and press the “Enter” key to get the random number between 10 and 30.


    randbetween example 1.3

  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.


    randbetween exampl 1.4

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 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.read more.

randbetween example 2.1

Now, open the RANDBETWEEN formula.

randbetween example 2.2

Next, supply bottom number and top number.

randbetween example 2.3

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.

RANDBETWEEN GIF 1

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.

randbetween example 3.1

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.

randbetween example 3.2

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.

Example 5

Step 2: Note that the first argument is YEAR. For this, open the RANDBETWEEN formula and supply years like 2015 and 2018.

Example 5.1

Step 3: Next, the second argument in the DATE function is MONTH. For this, open the RANDBETWEEN function and supply numbers 1 to 12.

Example 5.2

Step 4: Finally, 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.read more is DAY. For this supply, numbers from 1 to 31 for the Excel RANDBETWEEN function.

Example 5.3

Now, press the “Ctrl + Enter” keys. Again, we will get random dates in a selected range of cells.

RANDBETWEEN GIF 2

Important Things To Note

Frequently Asked Questions

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 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.read more with RANDBETWEEN.

randbetween example 4

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: –