Prev: Formula from consecutive rows to alternate rows?
Next: Why am I getting this VBA error?
From: Joe User on 25 Mar 2010 11:46
"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.
From: Joe User on 25 Mar 2010 12:46
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.
First  |  Prev  | 
Pages: 1 2
Prev: Formula from consecutive rows to alternate rows?
Next: Why am I getting this VBA error?