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
  • Mobile
  • 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? How to add in Excel-featured imageHow to Add or Sum in MS Excel 2016? count unique values featured imageHow to Count Unique Values in Excel 2016? Change Date Format featured imageHow to Change the Date Format 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 Add Hyperlink to Another Sheet in Excel?
  • How to Add and Remove Watermark in Excel
  • How to use Relative and Absolute Cell Reference in Excel?
  • How to Enable and Disable Scroll Lock in Excel?
  • How to Use INDEX and MATCH Functions in Excel?
  • How to Make Bar Graph in Excel?
  • How to Count Duplicate Values in Excel?
  • How to Wrap Text in Excel?
  • How to Round off Numbers in Excel?
  • How to Create a Bell Curve in Excel?

Related Searches

  • How to Bold, Itaticize, Strikethrough & Monospace Text in WhatsApp?
  • How to Configure Sudo wait time in Ubuntu 14.04
  • How to Whitelist in Outlook 2010
  • How to enable and configure gmail keyboard shortcuts
  • How to Protect Worksheet and Cells in MS Excel 2016?

Copyright © 2023 · QueHow · Contact Us