RANDBETWEEN Function in Excel: Explained
In this article, you will learn about the RANDBETWEEN formula and its uses in Excel.
What is the RANDBETWEEN formula in Excel?
The RANDBETWEEN function in Excel is a built-in function that generates a random integer between two specified values, inclusive of both endpoints. It is commonly used to generate random numbers within a specific range. A new number is calculated every time the worksheet is refreshed.
Syntax of the RANDBETWEEN formula in Excel
The syntax of the Excel RANDBETWEEN function is as follows:
bottom: (required) The smallest integer value this function will provide.
top: (required) The largest integer value this function will provide.
How to Use the RANDBETWEEN function in Excel?
Understand the RANDBETWEEN function better using an example case of student records to which we need to assign three-digit serial numbers randomly for a competition entry. Assume we issue three-digit serial numbers between 101 and 201 for students.
Mapping it with the RANDBETWEEN function syntax
bottom: The value of this attribute can be any three-digit number per the required criterion. Here we pick up 101 as the bottom number in the range. Hence the value of this attribute will be 101
top: Again, the value of this attribute can be any three-digit number per the required criteria. Assume the cap of the range as 201, which is greater than the bottom attribute number.
So, you need to insert the following formula in cell B2.
As you can see, a random serial number has been generated. To assign random numbers to all students in the list, we simply need to copy the formula to all other rows as required.
As shown in the picture, all students have been assigned three-digit serial numbers, and these numbers are re-calculated when the worksheet is refreshed.
Note: If you provide a wrong attribute value for the syntax of the RANDBETWEEN function, it shall result in an error. Consider an example shown below.
As you can see, the RANDBETWEEN function includes 5 as “bottom” and 2 as “top”. Therefore, the formula doesn’t work correctly and returns an error value.
When should you use the RANDBETWEEN Function in Excel?
The RANDBETWEEN function in Excel is useful when generating random integers within a specified range. It is commonly used when randomness is required, such as creating random samples for statistical analysis, developing random test data, simulating scenarios, or conducting random selections.
What is the difference between the RANDBETWEEN and RAND functions in Excel?
In Excel, the RANDBETWEEN and RAND functions generate random numbers, but they serve different purposes. The RANDBETWEEN function generates random whole numbers within a specified range in its arguments. On the other hand, the RAND function in Excel generates a random decimal number between 0 and 1.
