Microsoft Office Tutorials and References

In Depth Information

**RANDOM NUMBERS WITHOUT DUPLICATES**

=SMALL(H5:H14,RANDBETWEEN(1,5))

Of course, ﬁ guring out that there are ﬁ ve numbers left is more difﬁ cult than in

the above line. To make the formula compatible with Excel 2003,
PGC01
uses

RAND()
instead of
RANDBETWEEN
. The
RAND
function returns a random integer

between 0 and 0.99999.
=INT(RAND()*5)
returns a random integer between

0 and 4.
1+INT(RAND()*5)
returns a random integer between 1 and 5.

Because the 5 portion of that formula has to be different in each row of the

formula, you can use
(10-ROW()+ROW(B$4))
instead of hard-coding the 5.

Alternate Strategy:
Let’s say that you wanted to return a random check

number between 1501 and 1850 in cells H11:H35.

Note that the ﬁ rst random number cannot be generated in row 1 because there

always has to be a blank anchor cell above the ﬁ rst random number. In this

case, the ﬁ rst random number is in H11, so the anchor cell becomes H10.

The start of the formula refers to the anchor cell twice, once with a dollar sign

before the row and once without:

=SMALL(IF(COUNTIF(H$10:H10
,

Next, you specify the ﬁ rst and last numbers in the range twice:

=SMALL(IF(COUNTIF(H$10:H10,ROW($1501:$1850))<>1,ROW($1501

:$1850)),

Note:
You cannot use this method to generate numbers larger than 65,536 in

Excel 2003 and earlier. In Excel 2007, you are limited to 1,048,576.

For the ﬁ nal portion of the formula, you need to ﬁ gure out how many numbers

are in the pool of numbers. In this case, 1850–1501+1 is 350 numbers. Plug

in the 350 where shown and then plug in the address of the ﬁ rst cell in this

fragment of the formula:

Note: While the ﬁ rst portion of the formula uses H10 as the anchor cell, this

portion uses H11—the cell that contains the ﬁ rst random number.

1+INT(RAND()*(350-ROW()+ROW(H$11))))

The entire formula is:

=SMALL(IF(COUNTIF(H$10:H10,ROW($1501:$1850))<>1,ROW($1501

:$1850)),1+INT(RAND()*(350-ROW()+ROW(H$11))))

Type this formula in H11 and press Ctrl+Shift+Enter.

Copy cell H11 and paste it to H12:H35.

Tip: The sample ﬁ le for this chapter includes a worksheet where you can type

in three values, and the worksheet builds the formula for you.