Using RANDBETWEEN Function to Generate Random Numbers in Excel

Introduction To RANDBETWEEN Function

The RANDBETWEEN function in Excel is used to generate a random integer (whole number) between a lower and higher limit number.

You can use it to generate random numbers which you can use for simulations, sampling, or creating random data sets.

It’s a fairly straightforward function, so let’s dive into how to use it.

Understanding RANDBETWEEN Function

The syntax for the RANDBETWEEN  function is: =RANDBETWEEN(bottom, top)

Where, bottom and top are integer values that define the lower limit and upper limit, respectively, between which you want to generate the random number.

Both these arguments have to be whole numbers too, including negative numbers.

Let’s look at some practical examples of how this function works.

Practical Use Cases Of RANDBETWEEN

Here are some practical use cases of the RANDBETWEEN function in Excel:

Example #1: Generating A Random Number Between Any Two Values

All you have to do to generate a random number between any two numbers (in this case, 0 and 99) is to use this formula: =RANDBETWEEN(0,99)

Cells E3, E4, and E5 contain the same formula, but the function has generated a random number between the defined lower and upper values.

Example #2: Generating Random Decimal Numbers

Even though the RANDBETWEEN function only returns whole numbers, you can force it to return a random decimal number. You can achieve this by multiplying the lower and upper values by 10, 100, 1000, etc. and dividing by the same number for a random number that has one, two, three decimal places.

If you use the formula: =RANDBETWEEN(11*100,49*100)/100

The function will return a random number that has two decimal places.

Example #3:  Generating Random Text Strings

Since Excel uses the numbers 1-255 to represent characters, you can force the RANDBETWEEN function to return a string of random characters.

To do this, use the formula: =CHAR(RANDBETWEEN(1,255))&CHAR(RANDBETWEEN(1,255))&CHAR(RANDBETWEEN(1,255))

This returns the three random characters.

You can restrict/define which random characters to generate from by defining the arguments for RANDBETWEEN from the ANSI character table.

For example, this formula will restrict the random characters generated to mostly upper and lower case letters: =CHAR(RANDBETWEEN(65,122))&CHAR(RANDBETWEEN(65,122))&CHAR(RANDBETWEEN(65,122))

When using the RANDBETWEEN function the manner demonstrated in these examples, there are some things you should be mindful of.

Managing RANDBETWEEN Results

Unlike other functions that provide a static result, RANDBETWEEN generates a new random number every time the worksheet is recalculated or the cell is edited. By refreshing the random number with each calculation, RANDBETWEEN ensures that the data remains dynamic and responsive to changes in the worksheet.

However, this refresh behavior can also be a source of frustration if you need to maintain a consistent set of random numbers for a specific purpose. A simple solution to this problem is to use the Paste Special feature in Excel.

Try the Free Spreadsheet Extension Over 314,000 Pros Are Raving About

Stop exporting data manually. Sync data from your business systems into Google Sheets or Excel with Coefficient and set it on a refresh schedule.

Get Started

Using Paste Special To Fix Values Post-Randomization

Using Paste Special allows you to “lock in” the randomized values, preventing them from changing with subsequent calculations or formula updates.

Here’s how to implement this:

Step #1: Generate the initial random values using the RANDBETWEEN function.

Step #2: Select all the cells containing the randomized values, right click, and choose “Copy”.

Step #3: Right-click again (in the same cell or a different one) and select “Paste Special”, and then choose the “Values Only” option.

You will notice that as soon as you’ve pasted the values, the values in the cells with the RANDBETWEEN function change to a new set of random values.

Video Tutorial

Check out the tutorial below for a complete video walkthrough!

Handling Errors When Using RANDBETWEEN in Excel

Despite being simple to use, you could still run into situations where you get an error when using the RANDBETWEEN function.

Resolve the issues by tackling the reason why the error occurred.

Now that you understand how the RANDBETWEEN function works, how to use it, and handle common errors, let’s wrap it up.

Using The Excel RANDBETWEEN Function

The Excel RANDBETWEEN function can be used to generate a random whole number. And with a few clever modifications, it can generate decimal numbers or even a string of characters. We’ve also shown you how to resolve the issue of the RANDBETWEEN function recalculating values with every change or refresh.

Functions like this are relatively easy to use when handling small, static data sets. But what if you’re handling large amounts of dynamic data and want to make sure all your calculations are on point?

Say hello to Coefficient, the leading spreadsheet automation tool which can help you connect all your data sources, automate workflows, and share live insights within Excel. Get started today.