
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
SEE ALSO: Non Repeating Random Numbers  Random Sampler Excel Addin  Random Number Generator  Pseudo  Random Number Generator  Monte Carlo AddIn for Excel  Ozgrid Excel Plus Addin
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;
=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)
SEE ALSO: Non Repeating Random Numbers  Random Sampler Excel Addin  Random Number Generator  Pseudo  Random Number Generator  Monte Carlo AddIn for Excel  Ozgrid Excel Plus Addin
Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money
Special! Free Choice of Complete Excel Training Course OR Excel Addins 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
Some of our more popular products are below...
Convert Excel Spreadsheets To Webpages  Trading In Excel  Construction Estimators  Finance Templates & Addins Bundle  CodeVBA  SmartVBA  PrintVBA  Excel Data Manipulation & Analysis  Convert MS Office Applications To......  Analyzer Excel  Downloader Excel
 MSSQL Migration
Toolkit 
Monte Carlo Addin 
Excel
Costing Templates