Random Number Generation in Microsoft Excel

In this article, we will learn the random number generator in Microsoft Excel. To generate the random number, we will use the “RAND” and “RANDBETWEEN” function.

Rand function is used to returns the random number greater than or equal to 0 and less than 1, and also it recalculates all time when we do any activity in the sheet.

Syntax of “RAND” function:         =RAND()

For example:-

  • Select the cell A1 and write the formula.
  • =RAND(), press Enter.
  • The function can return any random number

img1

 

Note:- Random numbers change every time a cell on the sheet is calculated.

 

RANDBETWEEN:- This function is used to  extract a random integer number between the two specified (bottom & top) numbers. A new random integer number gets generated every time the worksheet is calculated.

Syntax of RANDBETWEEN:-  =RANDBETWEEN(bottom,top)

  • Select the cell A1 and write the formula.
  • =RANDBETWEEN(10,20), and press Enter.
  • Drag it with the mouse till the cell A5.
  • The function will return the random number in between 10, 20.

img2

 

This is all about how we can produce the random numbers by using the formula Rand and Randbetween in Microsoft Excel.

Let’s take an example on array of random numbers without duplicates

In case you want to generate random number in Excel within a specified upper & lower limit without VBA, then you will have to generate Random numbers with Excel Randbetween function. In this article, let’s discuss how to apply this function.

 

Question: I want to create a list of random numbers between 1 to 10 in a column without repetition.

 

  • In cell A2, the formula is
  • {=LARGE(ROW($1:$10)*NOT(COUNTIF($A$1:A1, ROW($1:$10))), RANDBETWEEN(1,11-ROW(A1)))}
  • Copy the formula in range A3:A11

 

Note: This is an array formula. Use CTRL + SHIFT + ENTER keys together
img3

 

  • The above formula will generate a list of unique random numbers from 1 to 10.

 

In this way we can use RAND & RANDBETWEEN functions in excel.

xlsx-1567

Download – Excel Random Number Generator – xlsx

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube