QueHow

A How-To and wiki Blog

  • Operating System
    • Windows
      • Windows 10
      • Windows 7
    • Linux
  • Software
    • Application Software
    • Microsoft Office
      • Outlook
      • Word
      • PowerPoint
  • Internet
    • Browsers
      • Mozilla Firefox
      • Google Chrome
    • CMS
    • Tips & Tricks
  • Website
    • ecommerce
    • Google
    • Social Networking
    • General Websites
  • Email
    • Gmail
  • Wiki

How to Generate Random Number in Excel?

By Neha T Leave a Comment

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:

  1. RAND() Function
  2. RANDBETWEEN() Function
  3. Random Number with Decimal Places
  4. Random Date
  5. Random Time
  6. 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 enter.

generate random number in excel 1

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.

generate random number in excel 2

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 enter.

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.

generate random number in excel 3

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 in excel 4

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 enter.

generate random number in excel 5

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 a random number in excel 5-1

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 enter.

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.

generate random number in excel 6

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 a random number in excel 6-1

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 enter.

generate random number in excel 7

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 a random number in excel 8

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 enter.

generate random number in excel 9

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.

generate a random number in excel 10

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.

You Might Also Like:

Sumif feature imageHow to SUMIF in Excel? Create a named range in Excel feature imageHow to Create Named Range in Excel? feature imageHow to COUNTIF in MS Excel 2016? How to Concatenate in Excel -featured imageHow to Concatenate Multiple Cells in MS Excel 2016? How to convert text to number in Excel - Featured ImageHow to Convert Text to Number in MS Excel 2016? Pie Chart in Excel featured ImageHow Create a Pie Chart in Excel? Highlight Duplicates in Excel-feature imageHow to Highlight Duplicates in Excel? Hide Formula in Excel - feature imageHow to Hide Formula in Excel?

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Most Viewed

  • How to Install and Use TELNET in Ubuntu
  • How to install Hindi fonts in MS Word
  • How to Change a File Extension in Windows 10?
  • How to create a flipkart account in 3 simple steps
  • How to Change Monitor Refresh Rate in Windows 10?
  • How to install IIS on Windows 10
  • How to Insert Clickable Checkbox in MS Word 2016?
  • How to Set Auto Reply in Outlook 2016?
  • How to Login as Administrator in Windows 10?
  • How to Check RAM Size and System Type in Windows 10?

Recent Additions

  • How to Remove Background Noise in Power Director?
  • How to Repost on Instagram Story?
  • How to Tag Someone on Instagram Post?
  • How to Fade Out Video in Power Director?
  • How to Tag Someone on Instagram Story?
  • How to Add Music on Instagram Story?
  • How to Save Video in Power Director?
  • How to Add Multiple Pictures on Instagram Story?
  • How to Post a Story on Instagram?
  • How to Unblock Someone on Instagram?

Related Searches

  • How to Configure Privacy Settings in Windows 10
  • How to Install and Configure Apache Web Server in Ubuntu
  • How to Enable Browser Cookies in Mozilla Firefox?
  • How to Add/Insert Sound in PowerPoint Presentation 2016
  • How to Switch Tabs in Google Chrome?

Copyright © 2021 · QueHow · Contact Us