Excel provides us with two formulas to generate random number those are, **RAND()** & **RANDBETWEEN()**. We can modify these functions to get random decimal numbers, random date, random time, random character. We can also calculate random text string to get a password. Well, there is a lot to do with this random function.

In this section, we will discuss the steps to:

- RAND() Function
- RANDBETWEEN() Function
- Random Number with Decimal Places
- Random Date
- Random Time
- Random Letter

## Steps to Generate Random Number in Excel 2016:

### Using RAND() Function

**Step 1:** Select a cell in an Excel sheet where you want to generate a random number and type the formula to generate the random number i.e.

**= RAND() // ***After typing the formula press ente*r.

**Step 2:** The RAND() function **returns** the **real number** which is randomly picked from the range ‘**0 to 1**‘. The random number generated is ‘**≥ 0**’ & ‘**< 1**’.

Whenever you conduct any action on the excel worksheet which have the RAND() function, it will generate a random real number on each action. Like, when you calculate any formula, or update a formula, or update data of a cell in a sheet, enter new data, etc. the worksheet recalculates itself which causes the RAND() function to recalculate itself again to generate a new random number each time.

In the figure below, you can observe the random number generated by the RAND() function.

### Using RANDBETWEEN() Function

**Step 1**: The RANDBETWEEN() function generate a random integer number from the range we specify. Select a cell to write the formula and type:

**= RANDBETWEEN(bottom-range, top-range) // ***After typing the formula press ente*r.

Here, the **bottom** range is the **smallest** integer that the RANDBETWEEN() function can return & **top **range is the **largest** integer that the RANDBETWEEN() function can return.

In the image below you can see that I have selected the bottom range as **1** and the top range as **1000**.

**Step 2**: The RANDBETWEEN() returns an integer number which is randomly picked from the range you provided to the function. Like, RAND() the RANDBETWEEN() function also recalculates itself every time the worksheet is recalculated. So, each time when some action is performed on the worksheet RANDBETWEEN(), returns a new random integer number.

The image below shows the random number generated by RANDBETWEEN(1, 1000) function. We can observe that the integer generated by this function lies between the range 1-1000.

### Generate Random Number with Decimal Places

**Step 1**: The RANDBETWEEN() formula generates the random **integer** number between the provided range. But, we can modify it to provide a **decimal number** between the provided range. We can generate a random number with **2 decimal places** using the RANDBETWEEN() formula :

**=RANDBETWEEN(1*100, 1000*100)/100 **// *After typing the formula press ente*r.

**Step 2**: In the formula above we have multiplied the range by 100 & the whole result is divided by 100. So, whatever the random integer number will be generated will get divided by 100. So, we will obtain the number with 2 decimal places.

As in the image below, you can see that the random number generated is with 2 decimal places. To generate a random number with one decimal place multiply & divide the range by 10, for 3 decimal place multiply and divide the range by 1000.

### Generate Random Date

**Step 1**: To generate a random date we can manipulate the RANDBETWEEN() formulas as

**=RANDBETWEEN(DATEVALUE(“30-Jun-2019”), DATEVALUE(“30-Jul-2020”))
**//

*After typing the formula press ente*r.

Now, here the **DATEVALUE()** function in takes the date in the **text form** and returns a **number** that **Excel recognizes as the date**. Now, both the DATEVALUE() functions in the RANDBETWEEN(), return the numbers corresponding to the date inside DATEVALUE() functions.

RANDBETWEEN() takes the returned numbers by DATEVALUE() functions as bottom and top range respectively. And generates a random integer between the bottom and top range.

**Step 2**: When the corresponding cell is in **Date format** Excel recognizes the** number returned by RANDBETWEEN()** as the ‘**Date**‘.

In the image below you can observe that we have obtained a random date **23 July 2019** because the cell is in Date format. If the cell is in **General cell format**, Excel recognizes date **23 July 2019** as the number **43669**.

### Generate Random Time

**Step 1**: To generate the random time we can build up the RANDBETWEEN() formula as:

**=RANDBETWEEN(TIMEVALUE(“3:45 AM”)*10000, TIMEVALUE(“4:32 PM”)*10000)/10000
**//

*After typing the formula press ente*r.

The **TIMEVALUE()** function returns a **decimal number** for the corresponding **text time** it has taken as an argument. It returns ‘**0**‘** for 12:00:00 AM** to ‘**0.9998**‘** for 11:59:59 PM**.

Now, we know that the RANDBETWEEN() takes two **integer** arguments as **bottom** range and **top** range respectively. So, we have multiplied the range (in TIMEVALUE() context) by 10000 & consecutively divided the result with 10000 to nullify the multiplication effect.

So, the** both TIMEVALUE()** function returns **two decimal numbers** which are **multiplied by 10000** to get them converted into **integers**. Further, the **RANDBETWEEN()** takes these integers as **bottom** and **top** range and returns a **random integer value** which is further divided by 10000 to again convert it into a decimal number.

**Step 2**: Now, the cell with this decimal number when seen in **Time** format exhibits the time for the corresponding decimal number.

### Generate Random Letter

**Step 1**: To generate the random letter we can modify the RANDBETWEEN() as follow:

**=CHAR(RANDBETWEEN(CODE(“A”), CODE(“Z”)))
**//

*After typing the formula press ente*r.

Now, here the for a **text string** **CODE()** returns the **numeric code for the first character in the text string**.

So, both the **CODE()** functions in the **RANDBETWEEN()** returns a **numeric code** which is an integer and serves as **bottom** and **top** range to **RANDBETWEEN()** function.

In turn, **RANDBETWEEN()** generates a **random integer** which is between the range provided by CODE() functions.

This random integer is served as an argument to** CHAR()** function which returns the** character corresponding to the integer number **returned by **RANDBETWEEN() **function.

So, this was all about how we can generate a random number in Excel. You can explore it more to get more formulas to generate random numbers in Excel.

## Leave a Reply