Microsoft Office Tutorials and References

In Depth Information

**Generating Random Numbers**

type some numbers into the cells, but it’s easier to let Excel ill in some realistic data for

you. The secret is to use either of the following two functions:

●
=RAND() uses no parameters and returns a random number between 0 and 1, carried

to 14 decimal places

●
=RANDBETWEEN(bottom,top) requires two parameters, which can be constants or

cell references; it returns whole numbers that are greater than or equal to
bottom
and

less than or equal to
top
.

Using either of these functions can require a little ingenuity and a combination of other

functions. If you’re building a worksheet to analyze sales at a restaurant or retail store, you

might want a large collection of data to simulate a series of typical sales. If the minimum

transaction is $25.00 and the maximum is $150.00, neither function alone will work. RAND

returns results that are well outside the range you’re looking for, while RANDBETWEEN

returns only whole numbers instead of the dollars-and-cents data you need. The solution?

Combine the two functions in a single formula you can use in the range containing sales

amounts:

=RANDBETWEEN(25,150)+ROUND(RAND(),2)

The first function generates a random whole number between 25 and 150, inclusive. The

second function generates a number between 0 and 1 and rounds it to two decimal places.

The combination equals a perfect mix of dollars and cents.