Randbetween function in excel

The RANDBETWEEN formula generates a random number between the specified numbers. It is derived from two words, random and between. Random means selecting any random number unknown to anyone, while between means finding the number between the specified ranges. The value returned will be from the specified range only. It cannot lie outside the specified range.

It is given by:

RANDBETWEEN(bottom, top)

Where,

Bottom: It represents the lower value of the specified range

Top: It represents the upper value of the specified range

It will return the integer number within the specified range of numbers in excel.

Condition: Bottom < Top or Bottom = Top

Bottom can never be greater than the top. Otherwise, excel will return an error.

Note: RANDBETWEEN() function accepts only integer values.

For example,

RANDBETWEEN(4,10)

Here,

4: Bottom

10: Top

It will display a random number between 4 and 10. It can be any number. It is generally used to find a lucky draw number in excel among various students or names in excel. We only need to type the formula in the form of ‘=RANDBETWEEN(bottom, top).’ We can set any range as per the requirements.

Let’s consider other examples.

a. RANDBETWEEN(-10, 0)

Value returned: -6

Explanation: The function will return any number between -10 and 0, i.e., -10, -9, -8, -7, -6, -5, -4, -3, -2, -1, and 0.

Here; the value returned is -6.

b. RANDBETWEEN(1000, 10000)

Value returned: 1141

Explanation: Here, the random number returned between the range 1000 and 10000 is 1141.

c. RANDBETWEEN(50, 200)

Value returned: 131

Explanation: Here, the random number returned between the range 50 and 200 is 131.

d. RANDBETWEEN(1, 100)

Value returned: 19

Explanation: Here, the random number returned between the range 1 and 100 is 19.

Important points about RANDBETWEEN()

• It does not work with characters in the form of names or alphabet. It only works with the data related to numbers.
  For example,
  RANDBETWEEN(2, 100)
  Value returned: 22
• We cannot specify cell name with gap of one or more cell in the function like other functions. It shows an error #VALUE!
  For example,
  RANDBETWEEN(A2, E2)
  Value returned: #VALUE!
  But, we specify the cell name with no cell gap.
  For example,
  RANDBETWEEN(A2, B2)
  Where,
  A2 and B2 contain the bottom and top values. There is no cell gap between the cells A2 and B2.
• If the top and bottom are equal, the function will return the same value.
  For example,
  RANDBETWEEN(1, 1)
  Value returned: 1
• The bottom number must be less or equal that the top number in the function. If the bottom is greater than the top, excel will return an error #NUM!
  For example,
  RANDBETWEEN(5, 2)
  Value returned: #NUM!
  It stands for
• It will return a new value each time we calculate.
  For example,
  RANDBETWEEN(100, 500)
  Try 1: 106
  Try 2: 258
  Try 3: 260
  We can see that every time we specify the same range within the function, it will return a new number each time.
• In case of decimal values, the function will return 1 by default.
  For example,
  RANDBETWEEN(0.5, 5.2)
  Value returned: 1
• Excel does not accept any blank space instead of integer values in place of bottom and top.
  For example,
  RANDBETWEEN( , 2)
  Value returned: #N/A
  It stands for Value not available error.

Examples

Let’s consider some examples.

Example 1: To find the lucky draw of students in a school with serial number 1 to 8.

Here, we will use the RANDBETWEEN() function that works as a lucky draw. The function returns a value which is not pre-known to anyone. It can be any random value between the specified ranges.

Consider the below steps:

1. Click on the cell in front of the lucky draw, as shown below:
   
2. Type ‘=RANDBETWEEN(1,8),’ as shown below:
   
   Here, 1 and 8 specifies the range given in the list as Serial Number (S No). The value returned will be matched with the prize money.
3. Press Enter. The returned value as the lucky draw will appear.
4. Thus, the prize money won by the candidate is rupees 5400. It is because the value returned is 3. The matching price with the serial number 3 is rupees 5400.

Example 2: Here, we will generate random number of various number ranges.

Consider the below table:

To generate the random number using the RANDBETWEEN() function, consider the following steps:

1. Click on the first cell of the random number column and type =RANDBETWEEN(A4, B4), as shown below:
   
2. Press Enter. The generated number will appear.
3. Drag and drop it to the last cell of the column, as shown below:
   
   The RANDBETWEEN() function will be applied automatically to all the selected cells based on the specified bottom and top values in the respective columns. The first column (A) will be considered as bottom value and second column (B) will be considered as top value. The function will generate desired output if the bottom value is less than the top value.

Thus, the RANDBETWEEN() function on each cell will work as:

=RANDBETWEEN(5,7)

Value returned: 5

=RANDBETWEEN(-2,10)

Value returned: 2

=RANDBETWEEN(-50,2)

Value returned: -16

=RANDBETWEEN(450,500)

Value returned: 495

=RANDBETWEEN(1200,25000)

Value returned: 21216

=RANDBETWEEN(0,5)

Value returned: 4

=RANDBETWEEN(-1,1)

Value returned: 1