Microsoft Office Tutorials and References
In Depth Information
RANDOM NUMBERS WITHOUT DUPLICATES
=SMALL(H5:H14,RANDBETWEEN(1,5))
Of course, fi guring out that there are fi ve numbers left is more diffi 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 fi rst random number cannot be generated in row 1 because there
always has to be a blank anchor cell above the fi rst random number. In this
case, the fi 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 fi 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 fi nal portion of the formula, you need to fi 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 fi rst cell in this
fragment of the formula:
Note: While the fi rst portion of the formula uses H10 as the anchor cell, this
portion uses H11—the cell that contains the fi 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 fi 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