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

Post a comment