Excel Formula Function - Pick a number, name or item at random.

Complete Excel Excel Training Course. Instant Buy/Download, 30 Day Money Back Guarantee

Current Special! Complete Excel Excel Training Course for Excel 97 - Excel 2003, only ~~$145.00~~. $59.95 Instant Buy/Download

Got any Excel Questions? Free Excel Help

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;

Enter a list of names in column A
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)

Excel Dashboard Reports & Excel Dashboard Charts 50% Off

Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to special@ozgrid.com 31 days after purchase date.

Instant Download and Money Back Guarantee on Most Software

Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!

Microsoft Ž and Microsoft Excel Ž are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft