Using Random Numbers in Excel

Vertex42 The Excel Nexus

Professional Excel Templates

Get Instant Access

You can use random numbers in many ways, for example: to add "noise" to a signal generated by a formula, to select items randomly from a list, or to perform a simulation by using the Monte Carlo method. These and some other uses of random numbers will be described in following sections.

Excel provides several ways to generate random numbers. The worksheet function RAND returns a random real number greater than or equal to 0 and less than 1. RAND is a volatile function; that is, a new random number is returned every time the worksheet is calculated. You can test this, after entering =RAND() in a cell, by pressing F9 (Calculate Now) or by typing anything (even a space character) in a cell and pressing the Enter key. You will see that the value returned by the RAND function changes.

The fact that random numbers are recalculated every time you do just about anything on a spreadsheet can sometimes be problematic, especially if your spreadsheet contains large ranges of such numbers. In the old days of 133-MHz computers, there could be a delay of several seconds while the spreadsheet recalculated. Fortunately, that's not usually a problem with today's high-speed computers.

But when a random number is used as input into a calculation and the random number keeps changing, that can be a problem. If you want to use RAND to generate a random number but don't want the number to change every time the worksheet is calculated, you must convert the formula to its value. You can do this by entering the formula =RAND() in a cell, copying the cell, and then use Paste Special (Values). This will convert the contents of the cell from =RAND() to a value (e.g., 0.743487098126025). Alternatively, you can type the formula =RAND() in the formula bar, then press F9, then Enter.

Instead of using the RAND worksheet function, you can use the RANDBETWEEN function, one of the Engineering functions. If this function does not appear in the list of functions in the Insert Function dialog box, or returns the #NAME? error when you use it in a worksheet formula, you must load the Analysis ToolPak add-in. After you load the Add-In, you will see a new function category, Engineering functions, in the Insert Function dialog box. As well as this new function category (which provide capabilities for working with imaginary numbers, or for converting between binary, hexadecimal and decimal number systems, among others), there are a number of new functions which are dispersed in other function categories: the RANDBETWEEN function is located in the Math & Trig category. The complete list of Engineering functions can be found in Appendix 5.

If you load the older Add-In, Analysis ToolPak, the function appears in the function list in uppercase (e.g., RANDBETWEEN). If you load the newer Add-In, Analysis ToolPak-VBA, the function list contains both the older uppercase function names and the newer function names, in lowercase. This helps to distinguish between Excel's built-in worksheet functions, such as RAND, and the Add-In names, such as Randbetween.

RANDBETWEEN(f>offo/n,fop) returns an integer random number. Bottom is the smallest integer RANDBETWEEN will return, top is the largest. For example, the expression RANDBETWEEN(0,100) returns (e.g., 74).

To generate a random number between bottom and top, without loading the Analysis ToolPak, use

For example, if bottom = 0 and top = 5, the returned result could be for example, 4.04608661978098.

To generate a random integer between bottom and top, use

=ROUND(RAND()*(iop - bottom) + bottom,0) For example, if bottom = 0 and top = 50, the returned result could be 27.

Since all of the above formulas include the RAND function, the returned result is volatile; that is, it changes each time the spreadsheet is modified.

Was this article helpful?

0 0

Post a comment