Random numbers

[john liem]

I want to generate numbers (1 to 15) in cells A1 through O1, but the
number in each cells should be unique compare to the other cells, how
can I do it? If I use =randbetween(1,15), I can not get unique number
in each cell, some numbers are duplicated.


--
john liem

[Bryan Hessey]

John,

The problem is that you want random, and unique which means not random.

You can achieve your results by putting
in cells A1 to O1
=int(rand()*99999999)

in cells A2 to O2 the numbers 1 to 15

in cells A3 to O3
=HLOOKUP(SMALL($A1:$O1,COLUMN()),$A1:$O2,2,FALSE)

This should give you a random shuffle each time the worksheet calculates (press F9)

I want to generate numbers (1 to 15) in cells A1 through O1, but the
number in each cells should be unique compare to the other cells, how
can I do it? If I use =randbetween(1,15), I can not get unique number
in each cell, some numbers are duplicated.


--
john liem

[joeu2004@hotmail.com]

Bryan Hessey wrote:
> The problem is that you want random, and unique which
> means not random.

That is incorrect. The OP simply wants random selection
"without replacement". Very common requirement. Nothing
less random about it.

> You can achieve your results by putting in cells A1 to O1
> =int(rand()*99999999)

.... Or simply =RAND().

> in cells A2 to O2 the numbers 1 to 15
> in cells A3 to O3
> =HLOOKUP(SMALL($A1:$O1,COLUMN()),$A1:$O2,2,FALSE)

That is a nice idea, but I don't believe it is guaranteed
to give unique values. Consider the rare case when RAND()
gives the same result twice. I believe HLOOKUP() will
return the same value from $A2:O2. The probability of that
is increased with Bryan's INT(RAND()*N) approach.

Normally I manually sort $A1:$O2 (in Bryan's example) based
on column A. If there are duplicates in $A1:$O1, the order
of corresponding unique values in $A2:$O2 is "arbitrary".
(But not unpredictable. It depends on the sort algorithm).

I would like to avoid the manual sort myself. Building on
Bryan's idea, if we could determine the column that the
SMALL() value came from, we could build a reference to
$A2:$O2 using OFFSET() or similar functions.

Offhand, I do not know how to do that. Hopefully, someone
else can offer a solution.

I believe that other threads on the same subject point to
one or another MVP's web site with solutions. Search for
"random" in the Excel newsgroups/forums.

-----

john liem Wrote:
> I want to generate numbers (1 to 15) in cells A1 through O1, but the
> number in each cells should be unique compare to the other cells, how
> can I do it? If I use =randbetween(1,15), I can not get unique number
> in each cell, some numbers are duplicated.

[joeu2004@hotmail.com]

joeu2004@hotmail.com wrote:
> Normally I manually sort $A1:$O2 (in Bryan's example)
> based on column A.

Duh, I meant to say "based on row 1" in Bryan's example.

I am used to putting related sets of numbers in columns,
not in rows as Bryan did. Generally I can see more rows
than columns on the display. It is easy for me to see
rows 1-15; harder to see columns A-O. So I would have
put =RAND() in A1:A15, 1-15 in B1:B15, and the appropriate
VLOOKUP() function in C1:C15. But that's just me.

Hmm, well, perhaps that is an important point to make.
It appears that the SORT tool can only sort by column,
not row. Is that really true!? (I'd be surprised.)

[Harlan Grove]

joeu2...@hotmail.com wrote...
>Bryan Hessey wrote:
....
>>You can achieve your results by putting in cells A1 to O1
>>=int(rand()*99999999)
>
>... Or simply =RAND().

Stronger statement in order. There's a small chance of duplication of
integers using INT(RAND()*99999999). There's *NO* chance of duplication
using RAND() alone when only 15 numbers are involved. NEVER round
pseudorandom numbers unless you need the rounded results. If they're
only used in intermediate calculations, *ALWAYS* leave them as-is
(unrounded).

>>in cells A2 to O2 the numbers 1 to 15
>>in cells A3 to O3
>>=HLOOKUP(SMALL($A1:$O1,COLUMN()),$A1:$O2,2,FALSE)
>
>That is a nice idea, but I don't believe it is guaranteed
>to give unique values. Consider the rare case when RAND()
>gives the same result twice. . . .

If the OP uses your simple =RAND() in row 1, this isn't an issue. The
only way you get duplicates from pseudorandom number generators is by
drawing more deviates than the period of the generator. The period of
Excel's generator is more than 1,000,000, so no duplicates in a range
of only 15 cells.

> . . . I believe HLOOKUP() will
>return the same value from $A2:O2. The probability of that
>is increased with Bryan's INT(RAND()*N) approach.

It's possible in Bryan's approach *ONLY* when using his truncated
integer random deviates.

>Normally I manually sort $A1:$O2 (in Bryan's example) based
>on column A. If there are duplicates in $A1:$O1, the order
>of corresponding unique values in $A2:$O2 is "arbitrary".
>(But not unpredictable. It depends on the sort algorithm).
>
>I would like to avoid the manual sort myself. Building on
>Bryan's idea, if we could determine the column that the
>SMALL() value came from, we could build a reference to
>$A2:$O2 using OFFSET() or similar functions.
>
>Offhand, I do not know how to do that. Hopefully, someone
>else can offer a solution.
....

If you have a single column, N row range D of distinct values, and you
want to draw a sample of size K <= N from D without replacement, then
all you need is another single column, N row range, RV, of distinct
random values (=RAND() sufficient for this) and formulas like

K1:
=INDEX(D,COUNTIF(RV,"<="&INDEX(RV,ROWS(K$1:K1))))

K1 filled down as far as needed.

[joeu2004@hotmail.com]

Harlan Grove wrote:
> There's *NO* chance of duplication using RAND()
> alone when only 15 numbers are involved.
> [....] The only way you get duplicates from
> pseudorandom number generators is by drawing
> more deviates than the period of the generator.
> The period of Excel's generator is more than
> 1,000,000, so no duplicates in a range of only
> 15 cells.

Good point. I thought of that, too. But I try to
not make ***-u-me-tions about algorithms that I have
no control over, and especially about undocumented
features. For example, what if RAND() later can
utilize a hardware RNG, if it is installed?

Don't get wrong: I must admit that your assertion
is quite likely right in the case of generating only
15 RAND() values. I was probably being a little
****. But I thought the point was important to make
since some people will apply these ideas to very
different circumstances.

[Harlan Grove]

joeu2004@hotmail.com wrote...
....
>Good point. I thought of that, too. But I try to
>not make ***-u-me-tions about algorithms that I have
>no control over, and especially about undocumented
>features. For example, what if RAND() later can
>utilize a hardware RNG, if it is installed?

Truly unknown algorithms are one thing. However, there's enough
documentation of Excel's PRNGs (both the one in Excel 2003 and the one
in previous versions) to establish that they *are* periodic. Undue
skepticism about documented functionality isn't wisdom, it's paranoia.
Whether paranoia is warranted when using Microsoft software with
Microsoft documentation is debatable.

Given the need for simulating sampling without replacement, would there
ever be hardware RNGs without a library routine to produce samples
without replacement?

>Don't get wrong: I must admit that your assertion
>is quite likely right in the case of generating only
>15 RAND() values. I was probably being a little
>****. But I thought the point was important to make
>since some people will apply these ideas to very
>different circumstances.

The period is over 1 million (per Microsoft's summary of the specs,
it's only about 1E6, but the details of those specs imply the period is
orders of magnitude greater). Myself, I can't see even thoroughly
misguided Excel users trying to generate *samples* on the order of a
million values.

****, yes. Important point, no.