## Excel RANDBETWEEN and RAND functions – How to generate random numbers and text

Microsoft Excel has two useful functions for generating numbers. The RAND function and RANDBETWEEN function.

RAND() function returns a random number between 0 and 1. Example: 0.600842025092928

RANDBETWEEN function returns values in a range you specify. The function has two arguments: *bottom* and *top*. RANDBETWEEN(*bottom*, *top*). *Bottom* is the lowest number in the range of numbers you want to return. *Top* is the highest number in the range.

Example, RANDBETWEEN(1,10) generates random numbers between 1 to 10.

**Formula:**

As shown above, there are duplicates. If you don´t want duplicates, read this post:

How to create a list of random unique numbers

### Generate numbers with a decimal

In this example, the RANDBETWEEN function generates random numbers between 2 and 4 with a decimal.

**Formula:**

Since it actually can´t generate numbers with decimals the function generates values between 20 to 40. Divide the output with 10 and the range becomes 2.0 to 4.0.

### Generate negative numbers with two decimals

In this example, the RANDBETWEEN function generates random numbers between -1 and -0.9 with two decimals.

**Formula:**

The function generates numbers between -100 and -90. The returned number is then divided by 100. The range becomes -1.00 to -0.90.

### Generate negative numbers with three decimals

Here, the RANDBETWEEN function generates random negative numbers between -1010.000 and -1000.000

**Formula:**

The function generates numbers between -1010000 and -1000000. The returned number is then divided by 1000. The range becomes -1010.000 to -1000.000

### Generate random names

**Formula:**

**Step 1 - Generate random value between 1 and 11**

RANDBETWEEN(1,11) returns a number between 1 to 11

**Step 2 - Return a value of the cell at the intersection of a particular row and column**

INDEX(array, row_num, [column_num])

=INDEX($A$3:$A$13, *random_value*)

The INDEX function let´s us return a value from a given range. The given range is $A$3:$A$13 and there are 11 cells in that range. Therefore, we need to return a random value between 1 and 11. RANDBETWEEN(1,11) returns a number between 1 to 11. The INDEX function then returns a random value from cell range $A$3:$A$13 in cell B3.

### Generate a random list

Let´s rearrange the names in cell A3:A13 in random order.

- Select cell range B3:B13
- Type =RAND()
- Press and hold CTRL + Enter

**Sort values**

- Select cell range A3:B13
- Right click on cell range
- Click Sort
- Click "Custom Sort..."

- Sort by: Column B
- Order: Smallest to Largest
- Click OK

### Create random text strings

**Formula:**

**Step 1 - Return a random number between 97 and 122**

RANDBETWEEN(97,122)

So why return a number between 97 and 122? All characters have a numeric code and the CODE function let´s you convert a character to a numeric code. For example, a is 97 and z is 122. By generating random numbers between 97 and 122 we can create characters from a to z.

**Step 2 - Return a character specified by a code number**

CHAR(RANDBETWEEN(97,122))

returns a random character from a to z.

**Step 3 - Combine four characters**

The ampersand operator & combines different functions to a text string.

=CHAR(RANDBETWEEN(97,122))& CHAR(RANDBETWEEN(97,122))& CHAR(RANDBETWEEN(97,122))& CHAR(RANDBETWEEN(97,122))

### Download excel *.xlsx file

### Functions in this post:

**INDEX(***array*, *row_num*, *[column_num]***)**

Returns a value or reference of the cell at the intersection of a particular row and column, in a given range.

**CODE(***text***)**

Returns a numeric code for the first character in a text string, in the character set used by your computer

**CHAR(***number***)**

Returns the character specified by the code number from the character set for your computer