Page 369 - Excel for Scientists and Engineers: Numerical Methods
P. 369
346 EXCEL: NUMERICAL METHODS
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
=SMALL( random, ROW()-I )
to sort the random numbers in ascending order. The expression ROW()-I 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 1 1. In cell D2 enter the formula
=MATCH(C2,random,O)
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.
Figure 15-4. A list of names randomized by using worksheet formulas.
(folder 'Chapter 15 Examples', workbook 'Randomize', worksheet 'By Formula')
