## Selecting Items Randomly from a List

You can use RAND to rearrange the values in a table so as to put them in random order, or to select a random sample from the table. There are two ways you can do this: either manually, using the Sort command, or by means of a formula. The former generates a randomized list that is "fixed"; that is, once randomized, the values in the list do not change. The latter method generates a list that will change each time the spreadsheet is recalculated. Clearly, there are advantages and disadvantages of either method.

To randomize manually, use =RAND() to generate a column of random numbers adjacent to (and most convenient, to the left of) the column of values to be randomized as shown in Figure 15-2.

 A B -y- random* Name 2 0.070428990 Agafwal, Brigitte 3 0.532482204 Ali, Le H A ' 0.833901457 Alvarado.V 5 0,090748668 Amato, Andreas A I 0.357788105 Antoine, Amy Louise 7 0.089596610 Atkinson, Sanya 8 0.801094843 Atwood, John 9 0.001795029 Barron, Johanna 10 0.210916288 Baumann, Carol Î1 0.936376433 Deaubrun, Jeffrey 12 0.624696117 Beaudoin, Samir 13 0.505254770 Belfiore, Danielle M 14 0.599828277 Blee, Kangrok 15 0 578697880 Bleeker, David M 16 0 796914834 Blute, Roxanne M

Figure 15-2. A list of names before randomizing. Only part of the list is shown, (folder 'Chapter 15 Examples', workbook 'Randomize', worksheet 'By Hand')

Figure 15-2. A list of names before randomizing. Only part of the list is shown, (folder 'Chapter 15 Examples', workbook 'Randomize', worksheet 'By Hand')

Then select the two columns and use the Sort command to Sort By the values in the column of random numbers. If the random number column is the leftmost column, you can use the Sort Ascending toolbutton i I. The randomized list is shown in Figure 15-3. To choose a random sample of N elements from the table, simply select, for example, the first N elements from the list.

 A B 1 random # Name 6 0 001795029 0.002546605 0005879847 0 009157058 0.013827644 Barron, Johanna Fournier, Elias T O'Reilly, John Dimattia, Tracey Ann Underkoffler, Anne E 7 0.017823817 Ullis, Soyon 8 0.026750475 Ditolla, J Patrick 0.046036729 0.056680621 Eaton, Jennifer Poon, Nicholas D 10

Figure 15-3. A list of names after randomizing. Only part of the list is shown, (folder 'Chapter 15 Examples', workbook 'Randomize', worksheet 'By Hand')

Figure 15-3. A list of names after randomizing. Only part of the list is shown, (folder 'Chapter 15 Examples', workbook 'Randomize', worksheet 'By Hand')

To sort by means of a formula, begin with the two columns as in Figure 15-2. The names random and Database were assigned to the ranges \$A\$:A139 and \$B\$:B139, respectively; the range references can be used if desired. In cell C2, enter the formula

to sort the random numbers in ascending order. The expression ROW()-1 would have to be modified if the formula wasn't entered in row 2—for example, ROW()-10 if the first row of the table were in row 11. In cell D2 enter the formula

=MATCH(C2,random,0)

to return the relative position of the returned random number in cell C2. In cell E2 enter the formula

=INDEX(Database,D2) to return the value at the same position in the array Database.

 A B C D E 1 random # Database sorted # pos Randomized 2 0 070428990 Agarwal, Brigitte 0.001795 8 Barron, Johanna 3 0.532482204 All, Le H 0.002547 50 Fournier, Elias T 4 0 833901457 Alvarado.V 0 00588 90 O'Reilly, John 5 0.D90748668 Amato, Andreas A 0.009157 39 Dimattia, Tracey Ann 6 0.357788105 Antoine, Amy Louise 0.013828 124 Underkoffler, Anne E 7 0 089596610 Atkinson, Sanya 0.017924 78 □Ills, Soyon 8 0.801094843 Atwood.John 0.02675 40 Ditolla, J Patrick 9 0.00179502S Barron. Johanna 0.046037 45 Eaton, Jennifer 10 0.210916288 Baumann, Carol 0.056681 96 Poon, Nicholas D

Figure 15-4. A list of names randomized by using worksheet formulas, (folder 'Chapter 15 Examples', workbook 'Randomize', worksheet 'By Formula')

Figure 15-4. A list of names randomized by using worksheet formulas, (folder 'Chapter 15 Examples', workbook 'Randomize', worksheet 'By Formula')

The preceding formulas can be combined into a single "megaformula" =INDEX(Database,MATCH(SMALL(random,ROW()-1),random,0)) to produce a more compact spreadsheet, as shown in Figure 15-5.

 ____L A __B c ! 1 random # Database Randomized 2 0.070428990 Agaiwal, Brigitte Barron, Johanna 3 0.532482204 Ali, Le H Fournier, Elias T 4 0.833901457 Alvarado.V O'Reilly, John 5 0.090748668 Amato, Andreas A Dimattla.TraceyAnn 6 0.357788105 Antoine, Amy Louise Underkoffler, Anne E 7 0 089596610 Atkinson, Sanya Lillis, Soyon 8 0.801094843 Atwood.John DJtolla, J Patrick 9 0,001795029 Barron, Johanna Eaton, Jennifer 10 0.210916288 Baumann, Carol Poon, Nicholas D

Figure 15-5. A list of names randomized by using a single "megaformula." (folder 'Chapter 15 Examples', workbook 'Randomize', worksheet 'By Formula')

Figure 15-5. A list of names randomized by using a single "megaformula." (folder 'Chapter 15 Examples', workbook 'Randomize', worksheet 'By Formula')

0 0