RANDBETWEEN(1,100) but not duplicate?

Hi seyedrezabazyar,

RAND and RANDBETWEEN can be compared to a physical draw of numbered balls, chips or slips of paper from a container, announcing (or recording) the number on the object, then putting the object back in the container, ready for the next draw.

RANDBETWEEN(1,100) is similar to a draw in which there are 100 objects in the container.

On the first draw, you are guaranteed the number drawn will not be a duplicate of one previously drawn.

Chances of not drawing a duplicate on the second draw are still great-odds are 99 to 1 you'll draw a number different from any drawn before.

By the time you've drawn 50 different numbers (placing each object back in the container after announcing/recording its number) you have just an even chance (50-50) of drawing a number that does not duplicate any previously drawn number.

And if you make it to draw of the last number, you'tr facing 99 to one odds against getting that last 1.

RAND has the same 'draw and replace' issues, but a much larger number pool (more than 100,000,000,000,000 different values). Using RAND won't "prevent duplicates," but in a run of 100 draws, it does make the chance of a duplicate small enough that "prevent" is a generally accurate description.

Here's a second method of placing the numbers 1 to 100 in random order on your 10x10 (plus a header row) table.

Like SGIII's, it uses RAND to generate 100 different values. From there it takes a different route, one that does the sorting automatically.

Table 1 generates the random list.

Table 2 transfers the values from column A of Table 1 to its 10x10 grid of cells.

Table 3 (optional) calculates a check sum. If no numbers are duplicated in the list (or in Table 2) The check sum will be 5050, the sum of all the integers from 1 to 100. If there are duplicates, that sum will be less than 5050.

Formulas:

Table 1::B2, and filled down to row 101: RAND

Table 1::A2, and filled down to row 101: RANK(B2,B,largest-is-high)

Table 2::A2, filled right to column J, filled down to row 11: INDEX(Table 1::$A,1+COLUMN()+10×(ROW()−2))

Table 3::A2: SUM(Table 1::A)

Table 3::A3: SUM(Table 2::A2:J11)

Late addition: I formatted cell A1 of Table 2 as a Checkbox cell. Clicking the cell toggles it from checked to unchecked (or from unchecked to checked) causing a recalculation of all three tables and placing the numbers in a new random order.

Using the document:

Like all formula based spreadsheets, these tables are always 'live' and will recalculate with each change to any of the tables. To get a stable set of the numbers 1 to 100, arranged in a 10x10 grid, you will need to do this:

1. Generate a set of numbers by opening the document or by clicking the checkbox.
2. Select cells A2 to J11 of Table 2. Copy
3. IF you have a table set up to receive this set of numbers:
   1. Select (ONE click) the top left cell of the block to receive the numbers.
   2. Go Edit (menu) > Paste Formula Results
4. IF you want to place the numbers in a New table:
   1. Click on an open space on the Sheet where you want to place the new table.
   2. Go Edit (menu) > Paste Formula Results
   3. Rename the new table as desired.

Regards,

Barry

An easy way to prevent duplicates is to list your 1-100 values in a column, and put the RAND() in the column next to them.

Then put this formula in A1 of your 10 x 10 table and fill right and down:

=INDEX(Table 1::$A,COLUMN()+(ROW()−1)×10)

Then sort on column B of table 1:

Your to 10 x 10 table will now have random values, with no duplicates:

Substitute ; for , in the formula if your region uses , as a decimal separator.

SG

Here is a way I created to make a list of random numbers with no duplicates:

select cell D1, then type (or copy and paste from here) the formula:

=ROWS(List Input::A)−COUNTIF(List Input::B, TRUE)−1

shorthand is:

D1=ROWS(List Input::A)−COUNTIF(List Input::B, TRUE)−1

E1=IF((COLUMN()−5) <$D1,COLUMN()−5, "")

To fill across, select cell E1, copy

select cells E1 thru the end of row 1, paste

make cell A1 be formatted as a checkbox

D2=IF($A$1, RANDBETWEEN(0, ($D$1−1)−(ROW()−2)), RANDBETWEEN(0, ($D$1−1)−(ROW()−2)))

E2=IFERROR(IF((COLUMN()−5)≥$D2, F1, E1), "")

fill E2 across by selecting cell E2, copy

select cells E2 thru the end of row 2, paste

B2=IFERROR(OFFSET($E1, 0, D2), "")

select row 2, copy

select row 2 thru the end of the table, paste

change the value in cell D1 to set how many random numbers you want, then click the checkbox to make the table update.

the random list is in column B

I think any solution that uses a random number generator without specifically making it impossible for there to be duplicates will have the possibility of duplicates. The possibility may be extremely low with RAND but it is still there. On the other side of the coin, I think any solution that makes it impossible to have duplicates also decreases/skews the randomness of the results, if ever so slightly. But it is the only 100% reliable way of ensuring no duplicates ever occur.

The solution below ensures there are no duplicates, ever.

Table 2 makes 100 absolutely unique random numbers, no possibility at all of a duplicate. Table 1 determines the rank of each of those numbers to give you your "random" numbers from 1-100, also with no possibility of a duplicate.

All cells of Table 2 have the formula =RANDBETWEEN(1,100)+ROW()÷100+COLUMN()÷10000

Table 1::A1 has the formula = RANK(Table 2::A1,Table 2::$A:$J) which is filled to the rest of the table

It is impossible for two values to be identical but there is a slightly higher probability that larger values will be in the lower rows and in the more rightmost columns. For instance, the random number 86 occurred in cells E3 and J5 and J7 of Table 2 and you can see in Table 1 how they got ranked.

EDIT: inserted the wrong screenshot the first time

Wayne Contello wrote:

did ya'll look at my solution? it's not possible to get duplicates with this method

Not carefully enough I have to admit. I retract my statement that methods for removing duplicates will skew the randomness. It appears yours will retain the full randomness. It does have some drawbacks (it is limited by the max number of columns in Numbers and it requires roughly N*N/2 IF statements) but it is clever and does the job.

Did y'all look at my solution. It's not possible to get duplicates with this method.🙂

It's a fairly common method for doing "sampling without replacement" in Excel and other spreadsheets. The key is to lay out the values in a column.

Two tables and two simple formulas.

SG

did ya'll look at my solution? it's not possible to get duplicates with this method

I may write this as an AppleScript but I always end up fighting with it and getting frustrated.

it is limited by the number of columns in a table