Random Numbers
I wrote:
A2:
=SMALL(IF(ISNA(MATCH(ROW(INDIRECT("1:20")),
$A$1:A1,0)), ROW(INDIRECT("1:20"))),
RANDBETWEEN(1,21-ROWS($A$1:A2)))
[....]
Note that A2 is an array formula.
If you prefer not to use RANDBETWEEN, the following array formula [1] should
work, which is also closer to Biff's paradigm:
=SMALL(IF(ISNA(MATCH(ROW(INDIRECT("1:20")),
$A$1:A1,0)), ROW(INDIRECT("1:20"))),
INT((21-ROWS($A$1:A2))*RAND())+1)
However, if we replace RAND() with 0.999999999999999 (15 9s), we will see
that this can return an error because INT unexpectedly returns 20. Actually,
the problem arises if RAND() returns any of the 9 values of the form
0.999999999999999+k*2^-53, for k=0 to 8.
Technically, this should not be a problem in Excel 2003 and Excel 2007
because some time ago, I had determined [2] that the largest RAND() result is
about 0.999999999999964, assuming the constants in KB 828795 [3] are correct.
(The smallest RAND() result is about 0.0000000000000359712259978551).
However, all bets are off with Excel 2010 and later, since RAND() uses a
completely different algorithm.
Some people will be quick to point out that it is extremely unlikely that
RAND() would return a value of 0.999999999999999 or larger anyway. But if we
want to have a bullet-proof formula, we could write the following array
formula [1]:
=SMALL(IF(ISNA(MATCH(ROW(INDIRECT("1:20")),
$A$1:A1,0)), ROW(INDIRECT("1:20"))),
MIN(21-ROWS($A$1:A2),
INT((21-ROWS($A$1:A2))*RAND())+1))
-----
Endnotes
[1] Enter an array formula by pressing ctrl+shift+Enter instead of just
Enter. In the Formula Bar, Excel will display the formula enclosed in curly
braces, viz. {=formula}. You cannot enter the curly braces yourself. If you
make a mistake, select the cell, press F2, edit as needed, then press
ctrl+shift+Enter.
[2]
groups.google.com/group/microsoft.public.excel.worksheet.functions/msg/ad5f41d4e55b7992, posted 11 Dec 2009 4:57pm (PT).
[3] support.microsoft.com/kb/828795
----- original message -----
"Joe User" wrote:
"The Rook" wrote:
I am wanting to generate random numbers from
1 to 20 in cells A1 to A20, but have no duplicates.
How can I do this?
A variation of Tony Valko's (Biff's) approach:
A1: =RANDBETWEEN(1,20)
A2:
=SMALL(IF(ISNA(MATCH(ROW(INDIRECT("1:20")),
$A$1:A1,0)), ROW(INDIRECT("1:20"))),
RANDBETWEEN(1,21-ROWS($A$1:A2)))
Copy A2 into A3:A20.
Note that A2 is an array formula. Enter an array formula by pressing
ctrl+shift+Enter instead of just Enter. In the Formula Bar, Excel will
display the formula enclosed in curly braces, viz. {=formula}. You cannot
enter the curly braces yourself. If you make a mistake, select the cell,
press F2, edit as needed, then press ctrl+shift+Enter.
To avoid having this formula change every time any cell in the workbook is
edited(!), put these formulas in another column, changing $A$1, A1 and A2
appropriately. Then copy the other column, and use paste-special-value to
put the values into A1:A20.
If you get a #NAME error, look at the RANDBETWEEN help page for instructions.
|