How do I generate an array of random numbers (that don't repeat)?
I know how to use RAND and RANDBETWEEN to generate single random numbers, but I'm looking for a way (w/ Excel 2007) to generate, as an example, six random numbers between 101 and 133, none of which repeat (i.e., I don't want two instances of 114).
Yea, I can do it manually with 6 cells of RANDBETWEEN . . . . . then copy & paste-value (to a separate set of cells) . . . . . then sort & look for duplicates . . . . . then repeat until I get no duplicates . . . . . but I may do this a number of times, and I'm wondering if there is a more elegant way of accomplishing this.
Would be nice to have a RANDBETWEENARRAY(101, 133, 6, 0), where the 3rd parameter is the size of the array, and the 4th parameter is 0=no repeats & 1=repeats allowed.
You could use an array formula to achieve this:
Enter your heading in cell A1
Enter the formula =RANDBETWEEN(101,133) in cell A2
Enter this array formula in cell A3:
=LARGE(ROW($101:$133)*NOT(COUNTIF($A$2:A2,ROW($101:$133))),RANDBETWEEN(1,(133+2-101)-ROW(A2)))
(press CTRL+SHIFT+ENTER to enter this as an array formula)
Now copy cell A3 down for as many rows as you require values and they should all be unique.
You can substitute the 101 and 133 in the above formula for any maximum and minimum values you want to use.
Would be nice to have a RANDBETWEENARRAY(101, 133, 6, 0), where the 3rd parameter is the size of the array, and the 4th parameter is 0=no repeats & 1=repeats allowed.
Another option on the future-Excel-wishlist would be to have a single-value random number generator where we tell it to exclude the values in certain other cells:
A6 = RANDBETWEENEXCLUDE(101, 133, A$1:A5), where the 3rd parameter tells the function what values (in what cells) to exclude.
This requires an ordered (non-recursive) sequence of cell evaluation, which may make that untenable.
No comments:
Post a Comment