Thursday, November 18, 2004

Using the RAND function in Excel

Excel has lots of mathematical, trigonometrical and statistical functions (amongst many other categories)

One of the more obsucure ones is the =RAND() function. Using ths function in a cell simply returns a random number between 0 and 1. I have found my default setting in excel generates a number with 15 decimal places eg 0.621329327946131.
If you cannot see that many decimal places (excel defaults to 9) then expand the number of decimal places.

Here are some ways I use the RAND function.

Generating dummy data.
I combine RAND with the ROUND function to give me the level of precision I want.
=ROUND(RAND(),3)*1000 will return a result between 1 and 1000.
=ROUND(RAND()*1000000,-3) will return an even hundred thousand result between 100,000 and 1,000,000.

Generating random passwords.
First create two columns as follows. A1-A26 fill with numbers 1 to 36, B1-B36 fill with a to z and 0 to 9. Select all this data and name this range "alphanum"
Now copy the following formula into a cell.
=VLOOKUP(INT(ROUND(1+RAND()*37,0)),alphanum,2,TRUE)&VLOOKUP(INT(ROUND(1+RAND()*37,0)),alphanum,2,TRUE)
This will return a two character password. Adding additional elements of &VLOOKUP(INT(ROUND(1+RAND()*37,0)),alphanum,2,TRUE) to the formula will increase the number of characters.


Posted by: Jethro | 11:52 PM |