Random numbers

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

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)

Originally Posted by 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 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 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.)

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.

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.

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.

Harlan Grove wrote:
> Undue skepticism about documented functionality
> isn't wisdom, it's paranoia.

In my case, it is based on decades of experience with
being on the oppposite side -- the person responsible
for implementing and supporting some functionality.
I know the value of the flexibility of undocumented
behavior -- the ability to evolve behavior judiciously.
I also know the "cop-out" value of undocumented behavior
-- the freedom not to support such behavior when it is
unwise to do so. And I know the danger of documenting
"too well" -- the inflexibility that can cause because
people depend on the documented behavior.

> Whether paranoia is warranted when using Microsoft
> software with Microsoft documentation is debatable.

My comments have nothing to do with the endless
parochial debates that some people like to engage in.
In fact, my comments were honed by experience with
software in another industry.

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

Sure! The hardware RNG I am familiar with does not.
Why should it? Why would you expect it of a hardware
RNG, if we don't see it with most software RNGs --
Excel, for example? ;-)

It is trivial to implement "sampling without
replacement" in almost any programming language, e.g.
VBA. So there really is no need to provide the
feature with any RNG implementation, be it hardware
or software.

joeu2004@hotmail.com wrote...
>Harlan Grove wrote:
....
>>Undue skepticism about documented functionality
>>isn't wisdom, it's paranoia.
>
>In my case, it is based on decades of experience with
>being on the oppposite side -- the person responsible
>for implementing and supporting some functionality.
>I know the value of the flexibility of undocumented
>behavior -- the ability to evolve behavior judiciously.
>I also know the "cop-out" value of undocumented behavior
>-- the freedom not to support such behavior when it is
>unwise to do so. And I know the danger of documenting
>"too well" -- the inflexibility that can cause because
>people depend on the documented behavior.

OK, but whether a PRNG is periodic or not is a fundamental operational
characteristic. When documented, it should be relied upon (in *EXACTLY*
the same way one should rely on numbers in Excel being carried to 15
and NO MORE THAN 15 decimal places of precision).

>>Whether paranoia is warranted when using Microsoft
>>software with Microsoft documentation is debatable.
>
>My comments have nothing to do with the endless
>parochial debates that some people like to engage in.
>In fact, my comments were honed by experience with
>software in another industry.

OK, so are you assuming you're the only participant in these newsgroups
with software development experience? And since the regulars span
occupational domains from aircraft design and manufacture, financial
services, academic statistics and mathematics as well as several
scientists and engineers, what other otherwise unrepresented industry
do you believe you represent?

>>Given the need for simulating sampling without
>>replacement, would there ever be hardware RNGs
>>without a library routine to produce samples without
>>replacement?
>
>Sure! The hardware RNG I am familiar with does not.
>Why should it? Why would you expect it of a hardware
>RNG, if we don't see it with most software RNGs --
>Excel, for example? ;-)
....

Precisely because software PRNGs (the 'P' stands for pseudo, and that
makes *ALL* the difference) are necessarily periodic. None have small
(<1E6) periods, so *ALL* are basically reliable for use in sampling
without replacement when population and sample sizes are smaller than
the period. It just requires a set of *UNROUNDED* deviates of the same
cardinality as the population from which you're sampling. So in Excel a
set of 15 RAND() calls *ALWAYS* and *RELIABLY* represents sampling from
more than 10^6 values strictly between 0 and 1 without replacement.
It's trivial to create 1-to-1 relations between such sets of deviates
and other sets of distinct values with the same cardinality.

Maybe some day in the distant future Excel will use hardware RNGs, but
it certainly doesn't now. Shouldn't you make use of current
*documented* functionality? Is there any sense in designing for
potential functionality that's unlikely to be available for years?

Thanks guys, and yes, I did realise there was a one in one hundred million chance of a duplicate, and maybe should have re-worked the item.

My original test was with Lookup (not HLookup) which appeared to be that
=LOOKUP(SMALL($A1:$O1,COLUMN()),$A1:$O1,$A2:$O2)
or
=LOOKUP(SMALL($A1:$O1,COLUMN()),$A1:$O2)
should have provided the answer with =Rand() in row 1 (A1 to O1)

but to no avail, it just wouldn't work as it seemed to be described (with or without a CSE enter).

So I started changing bits until it worked, (not really practical, but the reason that Lookup fails still eludes me) and the result was as originally shown.

Also, I agree that A1 to A15 is an easier to understand display, but the OP asked for A1 to O1.

Hopefully John Liem will return and gain something from the offerings posted.

cheers

Originally Posted by Harlan Grove

joeu2004@hotmail.com wrote...
>Harlan Grove wrote:
....
>>Undue skepticism about documented functionality
>>isn't wisdom, it's paranoia.
>
>In my case, it is based on decades of experience with
>being on the oppposite side -- the person responsible
>for implementing and supporting some functionality.
>I know the value of the flexibility of undocumented
>behavior -- the ability to evolve behavior judiciously.
>I also know the "cop-out" value of undocumented behavior
>-- the freedom not to support such behavior when it is
>unwise to do so. And I know the danger of documenting
>"too well" -- the inflexibility that can cause because
>people depend on the documented behavior.

OK, but whether a PRNG is periodic or not is a fundamental operational
characteristic. When documented, it should be relied upon (in *EXACTLY*
the same way one should rely on numbers in Excel being carried to 15
and NO MORE THAN 15 decimal places of precision).

>>Whether paranoia is warranted when using Microsoft
>>software with Microsoft documentation is debatable.
>
>My comments have nothing to do with the endless
>parochial debates that some people like to engage in.
>In fact, my comments were honed by experience with
>software in another industry.

OK, so are you assuming you're the only participant in these newsgroups
with software development experience? And since the regulars span
occupational domains from aircraft design and manufacture, financial
services, academic statistics and mathematics as well as several
scientists and engineers, what other otherwise unrepresented industry
do you believe you represent?

>>Given the need for simulating sampling without
>>replacement, would there ever be hardware RNGs
>>without a library routine to produce samples without
>>replacement?
>
>Sure! The hardware RNG I am familiar with does not.
>Why should it? Why would you expect it of a hardware
>RNG, if we don't see it with most software RNGs --
>Excel, for example? ;-)
....

Precisely because software PRNGs (the 'P' stands for pseudo, and that
makes *ALL* the difference) are necessarily periodic. None have small
(<1E6) periods, so *ALL* are basically reliable for use in sampling
without replacement when population and sample sizes are smaller than
the period. It just requires a set of *UNROUNDED* deviates of the same
cardinality as the population from which you're sampling. So in Excel a
set of 15 RAND() calls *ALWAYS* and *RELIABLY* represents sampling from
more than 10^6 values strictly between 0 and 1 without replacement.
It's trivial to create 1-to-1 relations between such sets of deviates
and other sets of distinct values with the same cardinality.

Maybe some day in the distant future Excel will use hardware RNGs, but
it certainly doesn't now. Shouldn't you make use of current
*documented* functionality? Is there any sense in designing for
potential functionality that's unlikely to be available for years?

Harlan Grove wrote:
> OK, so are you assuming you're the only participant
> in these newsgroups with software development experience?

This discussion is suffering from diminishing returns.
But just to clarify: I never put myself above anyone
else, nor did I disparage anyone. You questioned my
judgment; I explained the basis for my judgment. That
was all.

joeu2004@hotmail.com wrote...
....
>This discussion is suffering from diminishing returns.
>But just to clarify: I never put myself above anyone
>else, nor did I disparage anyone. You questioned my
>judgment; I explained the basis for my judgment. That
>was all.

You're the one who claimed you wanted to avoid a manual sort. I've
pointed out that if you have fewer than a million or so values, you
don't need a manual sort and CAN RELY on Excel giving you distinct
values from RAND. You're the one who then went off on the irrelevant
tangent of hardware RNGs.

Your experience with hardware RNGs is irrelevant to Excel or actually
deleterious to your suggested use of Excel (not relying on the periodic
nature of Excel's PRNG). In short, any judgment you may have developed
from your experience with hardware RNGs may actually lead to POOR
judgment when using Excel.

It's a public service to point out poor judgment in newsgroups.