Page 366 - Excel for Scientists and Engineers: Numerical Methods

CHAPTER 15 RANDOM NUMBERS & MONTE CARL0 METHOD 343

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 (eg, 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(bofforn,top) returns an integer random number. Bottom is
the smallest integer RANDBETWEEN will return, top is the largest. For example,
the expression RANDBETWEEN(0,lOO) returns (e.g., 74).
To generate a random number between bottom and top, without loading the
Analysis ToolPak, use

=RAND()*(top - bottom) + bottom.
For example, if bottom = 0 and fop = 5, the returned result could be for example,
4.04608661 978098.
To generate a random integer between bottom and top, use
=ROUND(RAND()*(fOp - bottom) + bOttOm,O)

For example, if bottom = 0 and top = 50, the returned result could be 27