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

| | Information Helpful? Why Not Donate.

TRY OUT: Smart-VBA | Code-VBA | Analyzer-XL | Downloader-XL | Trader-XL| More Free Downloads.. Best Value: Finance Templates Bundle

SEE ALSO: Non Repeating Random Numbers |Random Sampler Excel Add-in |Random Number Generator |Pseudo - Random Number Generator | Monte Carlo Add-In for Excel | Ozgrid Excel Plus Add-in

Excel has two usefulvolatile 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)

SEE ALSO:Non Repeating Random Numbers |Random Sampler Excel Add-in |Random Number Generator |Pseudo - Random Number Generator | Monte Carlo Add-In for Excel | Ozgrid Excel Plus Add-in

Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money

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

Instant Download and Money Back Guarantee on Most Software

Try out:Analyzer XL |Downloader XL |Smart VBA |Trader XL Pro (best value) |ConsoXL | MergeXL | O2OLAP for Excel | MORE>>

Excel Trader PackageTechnical 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 & Add-ins Bundle | Code-VBA | Smart-VBA | Print-VBA | Excel Data Manipulation & Analysis | Convert MS Office Applications To...... | Analyzer Excel | Downloader Excel | MSSQL Migration Toolkit | Monte Carlo Add-in | Excel Costing Templates