Thursday, December 9, 2010

Using the RAND Function

A very interesting and at times very useful function in Excel is the RAND function. This function returns a Random Number that is greater than or equal to 0 and less than 1. Each time your worksheet recalculates, (by reopening or forced recalculation by pressing F9), the RAND function returns a new random number. While some hard core statisticians have voiced concerns about the true Randomness of the RAND function, it suffices for nearly all but the most demanding statistical applications.

The syntax for the Rand function is simply:

RAND( )

If you want to create a random number between two numbers, (where a is the smallest number and b is the largest number), you can use the following:

=RAND()*(b-a)+a

If you want only Whole Numbers you can use:

RANDBETWEEN ()

For example, =RANDBETWEEN(1,200) will produce a random whole number between 1 and 200.

So how can you use this nifty little function? It can be used in myriad ways; for example, it can be used in conjunction with other functions to create a Password Generator. There are countless statistical applications, but you can also use it for entertaining applications. For example, one Excel Enthusiast used it create a Tetris-style game in Excel.

I have used the RAND function in conjunction with other functions and graphics to create a Slot Machine in Excel (send me a request at excelenthusiast@gmail.com if you would like a copy of the Slot Machine spreadsheet).

The RAND function. Great for use in statistical applications, as well as some RANDom FUN!

1 comment:

SiteAdmin said...

Thank You !!

Another application could be to be generate random dates. The function for the same is =TEXT(A2+INT(RANDBETWEEN(0,30)),"mm/dd/yyyy") to generate random dates between A2 and A2+30 days(where A2 is the start date)

Or you could use the function to run lucky draws