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 the pseudo-random numbers between a given range of numbers. Meaning these are the random numbers which are predicted with some mathematical algorithm in the backend. RANDBETWEEN is a volatile function in excel. Meaning, 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.
Syntax for RANDBETWEEN Function
Arguments of RANDBETWEEN Function:
bottom: A required argument that defines the smallest value that function could return.
top: A required argument that defines the largest value that function could return.
We will move towards 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 as 1 and top number as 10.
Step 3: Close the bracket and press Enter key to see the output.
You can see 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, then you just 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 top number 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, it is possible to generate random numbers with decimal as well.
Suppose we want to generate the 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 RANDBETWEEN formula in cell A2.
Step 2: While giving the bottom and top arguments, multiply both of 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 so that we can 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, just multiply the bottom and top value by 100. I want three decimals, 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 RANDBETWEEN and DATEVALUE function 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 randomly appear 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 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.
You can see, these dates are in the number formats. This is because Excel stores the date in its backend as a number of days from 01-01-1900. However, we need to convert these dates into a proper date format.
Step 4: Click on the Home tab and 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) is greater than the top (largest value) then 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 formula can allow you to generate the decimal output as well.
Recommended Articles
This is a guide to RANDBETWEEN in Excel. Here we discuss How to Generate RANDBETWEEN in Excel along with practical examples and a downloadable excel template. You can also go through our other suggested articles –
