Microsoft Office Tutorials and References
In Depth Information
RANDOM NUMBERS WITHOUT DUPLICATES
RANDOM NUMBERS WITHOUT
DUPLICATES
Challenge: You want to generate random numbers between 1 and 100 without
any duplicates. Excel offers the functions RAND() and RANDBETWEEN() , but
both of them are likely to generate duplicates, and you frequently need to
generate a series of random numbers without duplicates.
Background: I frequently use a three-column method to solve this problem,
but PGC01 posted an impressive formula to handle this problem.
To understand the formula, you have to understand how the SMALL function
works. Typically, SMALL returns the kth-smallest value in an array. For example,
=SMALL({60,10,20,30,40,50},3) returns 30, as 30 is the third-smallest
number in the list.
If the array specifi ed as the fi rst argument contains anything non-numeric,
those entries are ignored. =SMALL({60,10,FALSE,30,40,50},3) returns
40 because the 20 has been replaced by FALSE .
Solution: PGC01’s formula builds an array of the unused numbers in the
range and then selects from those numbers.
To illustrate, consider Figure 51.
Figure 51. Formulas generate six sets of the numbers, 1 to 10, randomly
sequenced.
 
 
Search JabSto ::




Custom Search