RANDBETWEEN

 The RANDBETWEEN function in Excel generates a random integer between two specified numbers. Below are 10 examples of how you can use the RANDBETWEEN function, including formulas and sample data.

1. Basic Random Number Generation

Generate a random number between 1 and 100.

excel

Copy

=RANDBETWEEN(1, 100)

2. Random Date within a Range

Generate a random date between January 1, 2020, and December 31, 2020.

excel

Copy

=DATE(2020, 1, 1) + RANDBETWEEN(0, 364)

3. Random Time within a Range

Generate a random time between 9:00 AM and 5:00 PM.

excel

Copy

=TIME(9, 0, 0) + (RANDBETWEEN(0, 480) / 1440)

4. Random Selection from a List

Randomly select a name from a list in cells A1:A10 .

excel

Copy

=INDEX(A1:A10, RANDBETWEEN(1, 10))

5. Random Decimal Number

Generate a random decimal number between 0 and 1.

excel

Copy

=RANDBETWEEN(0, 100) / 100

6. Random Password Generator

Generate a random 8-character password using uppercase letters.

excel

Copy

=CHAR(RANDBETWEEN(65, 90)) & CHAR(RANDBETWEEN(65, 90)) & CHAR(RANDBETWEEN(65, 90)) & CHAR(RANDBETWEEN(65, 90)) & CHAR(RANDBETWEEN(65, 90)) & CHAR(RANDBETWEEN(65, 90)) & CHAR(RANDBETWEEN(65, 90)) & CHAR(RANDBETWEEN(65, 90))

7. Random Boolean Value

Generate a random TRUE or FALSE value.

excel

Copy

=IF(RANDBETWEEN(0, 1) = 1, TRUE, FALSE)

8. Random Lottery Numbers

Generate 6 unique random numbers between 1 and 49 for a lottery.

excel

Copy

=SMALL(IF(COUNTIF($A$1:A1, ROW($1:$49))=0, ROW($1:$49)), RANDBETWEEN(1, 50-ROW(A1)))

(Note: This is an array formula and should be entered with Ctrl+Shift+Enter.)

9. Random Sampling without Replacement

Randomly sample 5 unique values from a list in cells A1:A20 .

excel

Copy

=INDEX(A1:A20, SMALL(IF(MATCH(A1:A20, A1:A20, 0)=ROW(A1:A20)-ROW(A1)+1, ROW(A1:A20)-ROW(A1)+1), RANDBETWEEN(1, 20)))

(Note: This is an array formula and should be entered with Ctrl+Shift+Enter.)

10. Randomize List Order

Randomize the order of a list in cells A1:A10 .

excel

Copy

=INDEX(A1:A10, RANK.EQ(B1:B10, B1:B10))

(Note: First, generate random numbers in column B using =RANDBETWEEN(1, 100) for each cell in B1:B10 , then use the formula above to sort the list based on these random numbers.)

Sample Data and Formulas in Excel

A	B	C
Name	Random Num	Random Date
John	=RANDBETWEEN(1, 100)	=DATE(2020, 1, 1) + RANDBETWEEN(0, 364)
Jane	=RANDBETWEEN(1, 100)	=DATE(2020, 1, 1) + RANDBETWEEN(0, 364)
Alice	=RANDBETWEEN(1, 100)	=DATE(2020, 1, 1) + RANDBETWEEN(0, 364)
Bob	=RANDBETWEEN(1, 100)	=DATE(2020, 1, 1) + RANDBETWEEN(0, 364)
Charlie	=RANDBETWEEN(1, 100)	=DATE(2020, 1, 1) + RANDBETWEEN(0, 364)

Notes:

• The RANDBETWEEN function will recalculate every time the worksheet is recalculated, so the random numbers will change.

• To prevent recalculation, you can copy the generated random numbers and paste them as values.

These examples should give you a good starting point for using the RANDBETWEEN function in various scenarios.