How to generate random numbers in excel

random-number-genarator

Random numbers are used in many operations like statistical sampling, complex calculations, etc. Random numbers have always been used to process numbers. Suppose there are 50 employees in a company if the management needs to assign a task, they can generate random numbers to assign each individual randomly to avoid confusion. In excel we have two functions called Rand() and Randbetween() to generate random numbers.

RANDBETWEEN(MIN,MAX)

Randbetween function is used to generate random numbers between a range of numbers, say between 0 to 9 this will generate random numbers between 0 to 9. The main purpose of this function is to genarate random number between two values.

Function:
=RANDBETWEEN(min,max)

This function gets two arguments bottom and top values which like minimum value to maximum value to generate random numbers.

RAND() Function

Rand function is used to generate random numbers greater than 0 and less than 1 (eg. 0.75468). The main purpose of this function is to generate a random number between 0 to 1.This function does not get any values as arguments.

Function:

=RAND()

Both rand() and randbetween() recalculates random number on open and if there is any change in the sheet, which is to be noted as values often change dynamically . in order to avoid excel from recalculating random numbers copy the values and paste it as paste special.

Method 1:

In order to generate random numbers between a range of numbers, say for example if you want to generate random numbers from 1 to 20, we should use randbetween() function in excel.

As per our example, the minimum value or bottom is 1 and the maximum is 20 or top, so your function should be,

=RANDBETWEEN(1,20)

random-number-genarator

The above function returned 13 as a random number between 1 to 20. (Press F9 to recalculate).

The .select property selects the last used cell in a row or column. As you can see the last used cell in column A is A13 which is selected. Once the cell is selected you can style the cell, copy to another cell or sheet, append row or column, etc can be made.

Method 2:

This method generates a random number greater than 0 but less than 1.

Function:

=RAND()

random-number-genarator

As you can see, the RAND() function returned a value between 0 to 1.

Note: These random values changes on recalculation, which mean values are not constant they are dynamic.

Try experimenting with these codes to get clarity.

Excel Logo

Download project file

Video: How to generate random numbers in excel

Share on:
Most Popular