## Excel’s RAND function

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

### 10 Responses to “Excel’s RAND function”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

How do you create a random number between -38 and +34 ?

My test sometimes produces numbers outside the low and high.

John

Does this produce numbers outside -38 and 34?

=RANDBETWEEN(-38,34)

Oscar, Thank you for your response.

Yes, the RANDBETWEEN does work with whole numbers.

I misstated the problem. I meant to state: random number between -38.5% and 34.1%. It is the RAND()function that creates numbers outside the High and Low percentages.

Sorry for the confusion.

John

I understand, try this:

=RANDBETWEEN(-385,341)/10

or this

=RANDBETWEEN(-340,340)/1000 and format the cells %.

10-4...worked fine. I used the 1000 divisor in order to create a true percentage result which is then used as a multiplier.

It sure looks simple when you see the answer...guess I just had a V8 moment.

(I still don't know why my RAND() function did not work properly, but am not willing to spend any more time trying to figure it out.)

Thanks again for your help. Keep up the good work.

JB

John,

thank you. I had to google both 10-4 and V8. :-)

How to create a random number and text

Example :

If select cell A1, then result below

KSPC00009000-KSPC00009999

If select cell B1, then result below

KSPR00009000-KSPR00009999

Atha,

Cell A1:

="KSPC0000"&RANDBETWEEN(9000,9999)

Cell B1:

="KSPR0000"&RANDBETWEEN(9000,9999)

Mr Oscar, thanks for your help, its work for me.

Mr Oscar,

How to Generate a New Number & Text to case below (VBA) :

textbox1 : save the result in Cell A2

commandbutton1 : Generate new random ie KSPC00009000-KSPC00009999

Generate a new number and not duplicate with range A2:A100

If there's a duplicate, then generate a new number again, until there's a unique value, save it and proceed to the next until it is finished.

I've tried this code but not running in case number & text

Thanks