Random Numbers
"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.
|