Random Sorting

You can use Excel to pick three winners-1^st, 2^nd, and 3^rd-chosen at random from a list in your spreadsheet. The easiest and fairest way to do this is to use Excel's RAND function in combination with its sorting capabilities.

Assume you have a three-column table in your spreadsheet, starting from column B and containing Name, Age, and ID No., in that order. You can place the RAND function in cell A2 and copy this down as many rows as needed, all the way to the end of your table. As soon as you do this, each cell in column A containing the RAND function will automatically return a random number by which you can sort the table. In other words, you can sort columns A, B, C, and D by column A in either ascending or descending order, and the three winners can be the top three names.

The RAND function is a volatile function that will recalculate automatically whenever an action takes place in Excel-e.g., entering data somewhere else, or forcing a recalculation of the worksheet by pressing F9. You'd better write down your winners quickly.

However, you can use this volatility to your benefit and record a macro that sorts data immediately after you recalculate and force the RAND function to return another set of random numbers. You then can attach this macro to a button so that each time you want to draw three winners, all you need to do is click the button and use the top three names.

For example, assume you have your data in columns B, C, and D and that row 1 is used for headings. First, place the heading RAND in cell A1. Enter =RAND( ) in cell A2 and copy down as far as needed. Then select any single cell and select Tools Macro Record New Macro....

Select columns A, B, C, and D and press F9 (to force a recalculation). Select Data Sort and sort the data by column A. Stop recording the macro.

Next, select View Toolbars Forms. Select a button from the Forms toolbar and place it anywhere on the worksheet. Assign the macro you just recorded to this button and click OK. (Change the text for the button from Button 1 to something more meaningful, if you want.) (You can select column A and hide it completely, as there is no need for a user to see the random numbers generated.) Each time you click the button, your data will be sorted randomly, and you can just read off the top three names to be the winners. See Figure 2-13.

Figure 2-13. The end result of a random sort with column A hidden

The RAND function in Excel 2003 has a major flaw. Although the Help clearly states the random number returned will be between 0 and 1, this is not always the case if the RAND function is used in many cells. Sometimes the RAND function will return a number less than 0. To read Microsoft's take on why they changed the algorithm, visit http://support.microsoft.com/default.aspx?kbid=828795.