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 speciﬁ ed as the ﬁ 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.