Random Function in Excel

Excel has two useful volatile functions that will produce random numbers. It has the RAND function, which returns an evenly distributed random number greater than or equal to 0 and less than 1. It also has the RANDBETWEEN function, which returns a random number between the numbers you specify.

*A Volatile function is a function that recalculates when you enter any data into any cell, or take any other action. One of the few exceptions is changing the format of a cell.

For example:

RAND FUNCTION

=RAND()

will produce a random number between 0 and 1. If you want a higher range you can use:

=RAND()*100

If you want only whole numbers you can use:

=INT(RAND()*100)

RANDBETWEEN

=RANDBETWEEN(1,500)

Will produce a random whole number between 1 and 500.

As you can see, both can produce pretty much any sort of random number. But what if you have a list of names and you want to select one at random? It easier than you may think! Follow these simple steps;

1. Enter a list of names in column A
2. Now enter the formula below in the cell you want the random name returned.

=INDEX($A:$A,RANDBETWEEN(1,COUNTA($A:$A)),1)

This will pick a name at random from your list in column A. It will also be dynamic in that when/if you add/remove names from the list they will automatically be included/excluded.

If you have a table of data (more than 1 column) and you wish to select an item at random from the table, you could use:

=INDEX($A:$C,RANDBETWEEN(1,COUNTA($A2:$A65536)),RANDBETWEEN(1,3))

This assumes your table 3 columns wide, hence; $A:$C and RANDBETWEEN(1,3) and we do not want row 1 includes as it contains headings, hence; COUNTA($A2:$A65536)

Source