How to Use RANDBETWEEN Function in Excel (4 Examples)

Overview of RANDBETWEEN Function

Today I will be showing how you can use the RANDBETWEEN function of Excel to get any random integer between two given numbers.

Read More: How to use RAND function in Excel (5 Examples)


RANDBETWEEN Function of Excel (Quick View)

Overview of RANDBETWEEN Function


Download Practice Workbook


Excel RANDBETWEEN Function (Syntax & Argument)


Summary

  • Returns a random integer between two given numbers, called bottom and top respectively.
  • The arguments can be both integer or fraction, but the output will always be an integer.
  • Includes the arguments. (If they are integers).
  • Is refreshed automatically. Each time you make any change to your workbook or worksheet, the return value will be changed.
  • Available from Excel 2003.

Syntax

Syntax of RANDBETWEEN Function

The Syntax of the RANDBETWEEN Function is:

=RANDBETWEEN(bottom,top)

Argument

Argument Required or Optional Value
bottom Required The lowest number between which you want the random integer. Can be an integer or a fraction.
top Required The highest number between which you want the random integer. Can be an integer or a fraction.

Note: The arguments can be either integer or fraction. But whether they are integer or fraction, the output will always be an integer.


Return Value

Returns a random integer between two given numbers. Includes the arguments bottom and top in case they are integers.


Excel RANDBETWEEN Function: 4 Examples


1. Getting Random Numbers Using the RANDBETWEEN Function

Look at the data set below. We have the names of 10 employees of a company named Sunshine Group.

A Data Set in Excel

Now we will provide each employee with an employee ID between 101 and 110.

We can provide that using the RANDBETWEEN function of Excel.

We enter this formula into each of the cells.

=RANDBETWEEN(101,110)

RANDBETWEEN Function with Integers

See, we have inserted an ID between 101 to 110 to each of the employees.

Formula Output Explanation
=RANDBETWEEN(101,110) 106 Returns a random integer between 101 and 110.

Note: There is a problem here. We want the employee IDs to be unique, right? That means each of the 10 employees will have a unique employee ID between 101 and 110. 

But using only the RANDBETWEEN function 10 times does not give any guarantee to provide 10 unique numbers.

As we can see, in the image above, 106 and 101 have been repeated. We do not want that.

To get a unique employee ID for each employee randomly, you can use this method of the combination of INDEX, FILTER, COUNTIF, RANDBETWEEN, ROW and ROWS functions.

  • First, fill up the first cell using a simple RANDBETWEEN function.
  • =RANDBETWEEN(101,110)

RANDBETWEEN Function in First Cell

  • Then insert this complex formula in the 2nd cell.
  • =INDEX(FILTER(ROW($A$101:$A$110),COUNTIF($B$4:B4,ROW($A$101:$A$110))=0),RANDBETWEEN(1,ROWS(FILTER(ROW($A$101:$A$110),COUNTIF($B$4:B4,ROW($A$101:$A$110))=0))),1)

Formula for Unique Numbers Using RANDBETWEEN Function

  • Then double click on the Fill Handle. You will find all the cells filled with unique random numbers from 101 to 110, like this:

Unique Numbers with RANDBETWEEN Function

  • Here I have used ROW(A101:A110) because I want random numbers between 101 and 110. You use it according to your needs. 

For example, if you want random numbers between 101 to 200, use ROW(A101:A200) or ROW(B101:B200) or so on.

  • B4 within the COUNTIF function is the first cell reference where I have inserted a number manually using a simple RANDBETWEEN function. You use your one.
  • FILTER function is available in only Office 365. So you can not use this formula unless you are in Office 365.

2. Getting Random Dates Using the RANDBETWEEN function

You can also get any random date between two dates using the RANDBETWEEN function.

Just put two dates in place of the bottom and top arguments instead of numbers.

Look at the data set below. We have the names of 10 candidates selected for an interview in the Sunshine Group.

A Data Set in Excel

Now we will enter random dates for their interviews in September 2021.

That means the dates need to be between 1-September-2021 and 30-September-2021.

The formula will be:

=RANDBETWEEN(DATE(2021,9,1),DATE(2021,9,30))

Formula for Unique Dates Using RANDBETWEEN Function

See, we have got a random date for each of the candidates.

Explanation of the Formula

  • DATE(2021,9,1) and DATE(2021,9,30) return the bottom and top values respectively, 1-Sep-2021 and 30-Sep-2021. See the DATE function for details.
  • RANDBETWEEN(DATE(2021,9,1),DATE(2021,9,30)) returns a random date between these two dates including these two.
Formula Output Explanation
=RANDBETWEEN(DATE(2021,9,1),DATE(2021,9,30)) 6-Sep-2021 Returns a random date between 1-Sep-2021 and 30-Sep-2021.

Note: Here we have the same problem as above. Some dates have been repeated. Like 6-Sep-2021 and 26-Sep-2021.

But we do not want that. We want a unique date for each of the candidates.   

How to do that?

No worries. We will accomplish this using a combination of Excel’s INDEX, FILTER, COUNTIF, RANDBETWEEN, SEQUENCE, ROWS, and DATE functions.   

First, fill up the first cell with a simple RANDBETWEEN function.

=RANDBETWEEN(DATE(2021,9,1),DATE(2021,9,30))

RANDBETWEEN Function with Dates

Then insert this complex formula in the 2nd cell.

=INDEX(FILTER(DATE(2021,9,SEQUENCE(30,1,1,1)),COUNTIF($C$4:C4,DATE(2021,9,SEQUENCE(30,1,1,1)))=0),RANDBETWEEN(1,ROWS(FILTER(DATE(2021,9,SEQUENCE(30,1,1,1)),COUNTIF($C$4:C4,DATE(2021,9,SEQUENCE(30,1,1,1)))=0))),1)

RANDBETWEEN Formula to Get Unique Dates

Then double click on the Fill Handle. You will see all the cells filled with unique dates from 1-Sep-2021 to 30-Sep-2021.

Unique Dates with RANDBETWEEN Function

  • Within SEQUENCE(30,1,1,1), 30 is the total number of dates within the interval. Here we have 1-sep to 30-Sep, a total of 30 days. You use your one.
  • The third argument 1 within SEQUENCE(30,1,1,1) denotes the starting day of your interval. For example, if you start on 15 September, use SEQUENCE(30,1,15,1).
  • Within the DATE function, 2021 and 9 denote the year and the month of the starting date respectively. For example, if you start in February 2022, use DATE(2022,2,...)
  • C4 within the COUNTIF function denotes the first cell reference where I inserted a date manually. You use your one.
  • The FILTER and the SEQUENCE functions are only available in Office 365.

3. Selecting Random Data from a Table

Now another case. Let us have a data set of the names and joining dates of 10 employees of the Sunshine Group.

Data Set in Excel

Now the chief of the company has decided to throw a surprise party on the joining date of any of the employees randomly.

How can we select one randomly?

Easy. Use this formula:

=INDEX(B4:C13,RANDBETWEEN(1,10),2)

RANDBETWEEN with INDEX in Excel

See, we have selected a date randomly from the table.

Explanation of the Formula

  • RANDBETWEEN(1,10) returns a random integer between 1 to 10. Here we have used 1 to 10 because we have a total of 10 employees.
  • INDEX(B4:C13,RANDBETWEEN(1,10),2) returns the cell content of the cell with the random row number and column number 2 (Joining Date) from the range B4:C13.
Formula Output Explanation
=INDEX(B4:C13,RANDBETWEEN(1,10),2) 31-Oct-2008 Returns the cell content with a random row number and column number 2 (Joining Date) from the range B4:C13.

4. Dividing into Groups Using the RANDBETWEEN Function

Now the final problem. Look at the data set below. We have a list of 12 countries qualified for the upcoming ICC Cricket World Cup in 2023.

Data Set of Excel

Now we will divide the teams into four groups, A, B, C and D.

You can use the CHOOSE function of Excel in combination with the RANDBETWEEN function to accomplish this.

 =CHOOSE(RANDBETWEEN(1,4),"A","B","C","D")

RANDBETWEEN with CHOOSE Function

See, we divided the teams into four groups.

Explanation of the Formula

  • RANDBETWEEN(1,4) returns an integer between 1 and 4. We have taken 1 to 4 because there are 4 groups.
  • CHOOSE(RANDBETWEEN(1,4),"A","B","C","D") assigns one group to each of the teams based on the result of the RANDBETWEEN function.
Formula Output Explanation
=CHOOSE(RANDBETWEEN(1,4),”A”,”B”,”C”,”D”) C First takes a random number between 1 to 4, then chooses one group according to the number returned by the RANDBETWEEN function.

If you notice carefully, you will find there is a problem too.

We want all the groups to have an equal number of teams, 3 in this case.

But here we have got 4 teams in groups A and C and 2 teams in groups B and D.

We do not want this.

But no worries. We will solve this too.

First, fill up the first cell randomly with the CHOOSE function.

RANDBETWEEN in One Cell

Then insert this complex formula in the 2nd cell.

=INDEX(FILTER({"A","B","C","D"},COUNTIF($C$4:C4,{"A","B","C","D"})<3),1,RANDBETWEEN(1,COLUMNS(FILTER({"A","B","C","D"},COUNTIF($C$4:C4,{"A","B","C","D"})<3))))

Equal Distribution into Groups Using RANDBETWEEN in Excel

Then double click on the Fill Handle. You will get all the cells filled with the groups, each group having 3 teams.

Equally Divided into Groups Using RANDBETWEEN

  • {"A","B","C","D"}are the names of my groups. You use anything according to your wish.
  • I have used COUNTIF($C$4:C4,{"A","B","C","D"})<3) because I want each group to have 3 teams. You use it according to your needs.
  • Within the COUNTIF function C4 is the cell reference of the first cell where I inserted the group manually. You use your one.
  • Finally, you can use this formula anywhere other than Office 365 because the FILTER function is only available in Office 365.                                                                                                                                                              

Limitations of Excel RANDBETWEEN Function

The output of the RANDBETWEEN function changes each time any other cell in the workbook or the worksheet is changed.

It may be any cell connected to the function or not.

To solve this problem, you have to turn the RANDBETWEEN outputs into normal text values before going to another task.

To do this, first select all the RANDBETWEEN outputs.

Selecting all the RANDBETWEEN Outputs

Then press Ctrl + C on your keyboard.

And then right-click on your mouse. From Paste Options, select Values.

Selecting Paste Value in Excel

You will find all the RANDBETWEEN outputs converted into text values.


Common Errors with Excel RANDBETWEEN Function

Error When They Show
#NUM! Shows when the bottom argument is less than the top argument.
#VALUE! Shows when any argument is of the wrong data type. For example, when the top or bottom arguments are text values in place of numbers.

Conclusion

Using these methods, you can use the RANDBETWEEN function of Excel to get any random integer between two numbers when necessary. Do you have any questions? Feel free to inform us.


Further Readings

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo