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.

Search JabSto ::

Custom Search