Excel RANDBETWEEN (Table of Contents)
Introduction to Excel RANDBETWEEN
When we speak about Excel, there is a function called RANDBETWEEN, which helps you to generate pseudo-random numbers between a given range of numbers. These are the random numbers predicted with some mathematical algorithm in the backend. RANDBETWEEN is a volatile function in Excel. It will change the values generated every time we refresh/newly define the formula over the same range. Excel RANDBETWEEN function generates a single random number at one iteration between a given range of numbers. For example, say, 10 random numbers between 1 to 6. This function, by default, generates only integer random numbers.
The syntax for RANDBETWEEN Function
Arguments of RANDBETWEEN Function:
bottom: A required argument defining the smallest value the function could return.
Top: A required argument that defines the largest value that the function could return.
We will move towards an example and see how RANDBETWEEN can be used differently.
How to Generate Random Numbers in Excel?
Let’s understand how to generate the RANDBETWEEN in Excel with some examples.
Example #1 – RANDBETWEEN to Generate Random Numbers
Suppose you want to generate random numbers between 1 to 10. You can use RANDBETWEEN to do so.
Step 1: Start typing the RANDBETWEEN formula in cell A2.
Step 2: Enter the bottom number 1 and the top 10.
Step 3: Close the bracket and press Enter key to see the output.
A single random number between 1 to 10 is generated under cell A2.
Step 4: If you want to generate 10 random numbers between 1 to 10, drag the formula across the next 9 rows (until A10). See the screenshot below.
Example #2 – RANDBETWEEN to Generate Negative Numbers
We can also use negative numbers as an argument to generate random numbers. Meaning we can generate a random number between -5 and +5.
Step 1: Start typing the RANDBETWEEN formula in cell A2.
Step 2: Enter the bottom number as -5 and the top as 5.
Step 3: Complete the formula by closing parentheses and Press Enter to see the output.
You can also generate multiple random numbers between -5 to 5. See the screenshot below.
As said earlier, the function is volatile; you may see different results every time the formula gets refreshed, or the sheet is opened.
Example #3 – RANDBETWEEN to Generate Random Numbers with Decimals
RANDBETWEEN usually generates integer random numbers between the ranges. However, with some modifications under formula arguments, generating random numbers with decimals is possible.
Suppose we want to generate random numbers with one decimal after the integer part between 5 to 15. We will see how it can be done step by step.
Step 1: Start typing the RANDBETWEEN formula in cell A2.
Step 2: While giving the bottom and top arguments, multiply them with 10 and close the parentheses as we want the output as one decimal after the integers. See the screenshot below.
Step 3: Now, divide by 10 to the entire formula to get decimal output and press the Enter key.
You can generate multiple random numbers by copying the formula. If you want the data up to two decimal places, multiply the bottom and top values by 100. I want three decimals to multiply by 1000 and so on.
Example #4 – RANDBETWEEN and DATEVALUE to Generate Random Date
We can also generate random dates using a combination of the RANDBETWEEN and DATEVALUE functions in Excel. Follow the steps below:
Suppose we want to generate random dates between August 01, 2019, and August 27, 2019. Follow the steps below to do so.
Step 1: Select all the rows where you want the dates to appear randomly, and in the first cell, start typing the RANDBETWEEN formula in cell A1.
Step 2: Use DATEVALUE to input the bottom date as August 01, 2019, and the top date as August 28, 2019. Make sure the dates are in Excel compatible date format.
Step 3: Press CTRL + Enter to apply this formula under all selected cells and generate random dates.
Excel stores the date in its backend as several days from 01-01-1900. You can see these dates are in several formats. However, we need to convert these dates into a proper date format.
Step 4: Click the Home tab under the number formatting section, change the format to Long Date, and press Enter key.
You’ll be able to see the dates generated randomly between August 01, 2019, to August 14, 2019, as shown below.
This is from this article. Let’s wrap things up with some points to be remembered.
Things to Remember About RANDBETWEEN in Excel
- It generates random numbers between the bottom and top numbers.
- If the bottom (smallest value) exceeds the top (largest value), you will get a #NUM! Error in the RANDBETWEEN formula.
- It is a volatile function; therefore, every time the sheet gets refreshed/calculated, it will change the values for random numbers under a given range.
- To stop the formula from being volatile/calculating sheet every time, we need to press F9 when the RANDBETWEEN formula is completed under the formula bar. Or we can paste our results as values to remove the threat of the sheet being calculated.
- This function can only generate Integer numbers by default. However, some amendments under the formula can also allow you to generate the decimal output.
Recommended Articles
This is a guide to RANDBETWEEN in Excel. Here we discuss How to Generate RANDBETWEEN in Excel, practical examples, and a downloadable Excel template. You can also go through our other suggested articles –
