RANDBETWEEN Function in Excel

Just like the RAND function in excel, the RANDBETWEEN function is also an in-built excel formula that is useful to create random numbers in excel.

In this blog, we would learn how to generate random numbers between two numbers using the excel RANDBETWEEN formula.

So, let us begin 😎

When to Use RANDBETWEEN Function in Excel

The Excel RANDBETWEEN function is an in-built math/trig function that is used to create, generate and return random numbers (integers) between the top and bottom values in excel.

The outcome of this function is an integer value between the specified top and bottom numbers.

Syntax and Arguments

=RANDBETWEEN(bottom, top)

There are two arguments of the RANDBETWEEN excel function, as explained below:

  • bottom – In this argument, enter the starting integer value (smallest number).
  • top– In this argument, enter the ending integer value (largest number).

This excel function would return the random number between the starting and ending values.

Using Excel RANDBETWEEN Function with Examples

Now, to start with, let us take one very basic example of the RANDBETWEEN excel formula.

Let us find a random number between 10 and 50. To achieve this, simply use the following formula in an excel cell.

RANDBETWEEN Function Example

As a result, excel would return an integer value randomly between the bottom and top values (10 and 50). In above example, it returned 43. However, the output integer value may be some other value in your case.

With you, you have a basic understanding of how the RANDBETWEEN formula works in excel.

Let us now learn how to generate random numbers between two numbers in multiple cells.

Infographic - RANDBETWEEN Formula Function in Excel

Generate Numbers Between Two Values in Cell Range

There are two ways to generate random numbers between two values in multiple cells in excel.

  • At first, select the cell range. Then, type the =RANDBETWEEN(10,50) formula and use Ctrl + Enter keyboard keys. This would generate the integer values between the lower and higher values in all the selected cells at once.
Random Numbers Between Two Values in Cell Range
  • The alternate way is by using the excel fill handle tool. Type the =RANDBETWEEN(10,50) formula in the first cell and press Enter. Finally, drag the formula to the cells below it with the help of the fill handle tool, like this:
Using Fill Handle Tool with RANDBETWEEN

Stop Formula To Change Value Each Time

Like the RAND excel function, the excel RANDBETWEEN formula is also a volatile excel function. This means that the formula value gets updated each time when you type anything in any other cell or open the workbook again.

There are two ways to stop excel re-generate the random number each time, use below procedure:

Generate the random numbers using the RANDBETWEEN function, and then use the paste special excel feature to copy and paste the random numbers as values. This would remove the formula from the selected cells and keep the cell values.

Excel Paste Special Values Random Numbers

RAND and RANDBETWEEN Functions – Difference

There are three major differences between these excel functions – RAND v/s RANDBETWEEN:

  • The RAND function has an empty parenthesis. It means that it does not accept any input argument. However, the RANDBETWEEN function accepts two arguments – bottom and top.
  • The RAND formula returns the random number between 0 and 1, whereas RANDBETWEEN function returns number between the specified bottom and top values.
  • The RAND function returns a decimal value, whereas the RANDBETWEEN function only returns an integer value.

Do Not Miss This Point

When you specify the bottom argument greater than the top argument, the RANDBETWEEN function will return the #NUM! error code. To resolve the #NUM! error code, make sure that the top value is always a lower value than the bottom value.

#NUM! error in RANDBETWEEN Excel Function

Thank You 🙂

Leave a Comment