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.

You may like these other stories...

Earlier this week I presented the Chart Edition of AccountingWEB’s High Impact Excel webinar series. One of the many topics I covered was the Sparklines feature, which was first introduced in Excel 2010. Several...
On January 30, I led a free, one-hour webinar, High Impact Excel: Pivot Table Edition. If you missed the presentation, it’s too late to get CPE credit, but you can watch an on-demand recording. After the webinar, I...
By David Ringstrom, CPA In Part 1 of this series I showed how to use a custom number format to conditionally display decimal places. Although the technique is simple, the downside is it may not work in every situation....

Already a member? log in here.

Upcoming CPE Webinars

Aug 21
Meet budgets and client expectations using project management skills geared toward the unique challenges faced by CPAs. Kristen Rampe will share how knowing the keys to structuring and executing a successful project can make the difference between success and repeated failures.
Aug 26
This webcast will include discussions of recently issued, commonly-applicable Accounting Standards Updates for non-public, non-governmental entities.
Aug 28
Excel spreadsheets are often akin to the American Wild West, where users can input anything they want into any worksheet cell. Excel's Data Validation feature allows you to restrict user inputs to selected choices, but there are many nuances to the feature that often trip users up.
Sep 9
In this session we'll discuss the types of technologies and their uses in a small accounting firm office.