Excel Tip: Find Random Integer Within a Selected Range | AccountingWEB

Excel Tip: Find Random Integer Within a Selected Range

Excel's random number generating function, RAND, normally produces a random number that is a five- or six-digit decimal greater than or equal to 0 and less than 1. The function appears in your spreadsheet like this:

=RAND()

With a little customization, you can tailor this function to provide integers instead of decimals, and you can request the range within which the results should fall.

For example, if you want to find a random number between 1 and 500,000, the formula would look like this:

=INT(RAND()*(500000-1)+1)

A random number between 100 and 1,000 would be generated with this function:

=INT(RAND()*(1000-100)+100)

To find additional random numbers within the specified range, press the F9 function key and the random number function will recalculate.

Note that each time you recalculate or re-open a worksheet containing the random number function, the function will recalculate. Should you want to retain a random number that was generated with this function, enter the number without the function in a separate cell.

Excel Add-In

If you have installed Excel's Analysis ToolPak, there is a function in the ToolPak that performs this procedure. It is the RANDBETWEEN function, and you would use it like this:

=RANDBETWEEN(1,500000)

The Analysis ToolPak can be installed by selecting Add-Ins on the Tools menu, then requesting the ToolPak. You will need your installation CD to install this feature.

Wait, there's more!
There's always more at AccountingWEB. We're an active community of financial professionals and journalists who strive to bring you valuable content every day. If you'd like, let us know your interests and we'll send you a few articles every week either in taxation, practice excellence, or just our most popular stories from that week. It's free to sign up and to be a part of our community.
Premium content is currently locked

Editor's Choice

WHAT KIND OF FIRM ARE YOU?
As part of our continued effort to provide valuable resources and insight to our subscribers, we're conducting this brief survey to learn more about your personal experiences in the accounting profession. We will be giving away five $50 Amazon gift cards, and a $250 Amazon gift card to one lucky participant.
This is strictly for internal use and data will not be sold
or shared with any third parties.