There are two ways to generate a random number between a range in Excel:
Method 1: Use RANDBETWEEN() to Generate Random Integer in Range
=RANDBETWEEN(1, 10)
This particular formula will generate a random integer between 1 and 10.
Method 2: Use RAND() to Generate Random Number with Decimal in Range
=RANDBETWEEN(5, 14) + RAND()
This particular formula will generate a random value with decimal places between 5 and 15.
The following examples show how to use each method in practice.
Step 1: Use RANDBETWEEN() to Generate Random Integer in Range
We can use the following formula to generate a random number between 1 and 10:
=RANDBETWEEN(1, 10)
We’ll type this formula into cell A1 and press Enter:
The RANDBETWEEN() function generated a random integer of 9.
We can also click and drag this formula down to other cells in the same column to generate a list of random integers between 1 and 10:
Each of the cells in column A now contains a random integer between 1 and 10.
Example 2: Use RAND() to Generate Random Number with Decimal in Range
We can use the following formula to generate a random number with decimal places between 5 and 15:
=RANDBETWEEN(5, 14) + RAND()
We’ll type this formula into cell A1 and press Enter:
The formula generated the value 8.22564.
Here’s how this formula actually works:
- First, RANDBETWEEN() generates a random integer between 5 and 14.
- Then, RAND() generates a random decimal between 0 and 1 and adds it to the integer.
The result is a random value with a decimal between 5 and 15.
Note that we could also drag and fill this formula down to other cells in column A:
Each of the cells in column A now contains a random value with a decimal between 5 and 15.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
How to Randomly Select Cells Based on Criteria in Excel
How to Select a Random Sample in Excel
How to Calculate Average If Between Two Values in Excel