In a class, someone asked about a random number generator. I replied with information about previous iterations of the RAND() function built into Excel. In researching it, I realized that Excel has moved on. Hence this post.
=RAND() in a cell will display a random number between 0 and 1. The function is volatile, meaning that each time you press Enter to edit a cell or press F9 to recalculate the workbook, the RAND() function displays a different random number.
If you want the number between 0 and 100, use the formula: =RAND()*100. This will display the number, but it will contain a fraction. You can either embed it in a ROUND() function (=ROUND(RAND()*100),0) or use the RANDBETWEEN function, which is preferable.
RANDBETWEEN lets you enter a upper and lower number and generates random numbers between and including those two numbers. The formula =RANDBETWEEN(6,12) will generate whole numbers between 6 and 12.
For a more complex application, suppose you have a list of Employees, including their Employee ID number and want to select a person randomly from the list. If no one can be selected twice, i.e., once you’re selected, you cannot be selected again, you might combine a VLOOKUP with RAND and a SELECTED column as shown above.
In the example above, cells A1:D30 contain a list of employees, with their ID number in A and a Selected column in Y. Press F9 to select a winner.
The function in G1 selects a random number between 1003 and 1030. (The formulas appear in G5 and G6.) If your list contains holes (i.e., numbers not used, such as 1000, 1001, 1002 in this list), the message “Press F9 again” will appear and will instruct the user to try again. If the person has already won, the message “Already Won. Try Again.” will appear. Keep pressing F9 until you have a winner.
Important: once you have a winner, write the number down because if you do any editing of the spreadsheet (such as marking the winner), the RAND function will recalculate and you’ll lose the first winner.
The formula in G1 selects a random number between 1000 and 1030.
The formula in G2 looks complex, but it breaks down simply:
First, you look up the ID number. If it is not found
“ISERROR(VLOOKUP(G1,$A$2:$D$29,3,FALSE),“Press F9 Again”)”
display the message “Press F9 Again”.
Otherwise, it is found so you look for a Y in Column D:
IF(VLOOKUP(G1,$A$2:$D$29,4,FALSE)=”Y”,”Already Won. Try Again”
If the “Y” is found, display the message ”Already Won. Try Again”.
If there is no “Y”, display the winner’s last name: “VLOOKUP(G1,$A$2:$D$29,3,FALSE)”