Lois Patterson, in her book 'Teach Yourself Excel 97' sets an exercise to
create a Lottery Number selector with a set of six random numbers.
=INT(45*RAND)+1 This is fine and easily done.
It goes on to say that by using the IF function it is possible to prevent
the generated numbers being duplicated in the set. After weeks of
frustration I am unable to solve this problem. Please can anyone help?

Doesn't that contradict the fundamental idea of random numbers? A
truly random number is one that has exactly the same chance of
appearing in a distribution as any other number. By preventing
duplicates you'd be introducing non-randomness.

Will you be applying this information in a lottery ticket exercise,
btw?

J.E. McGimpsey has a User Defined function that can work for you:

You can find it he
http://www.mcgimpsey.com/excel/udfs/randint.html

If you're new to macros, you may want to read David McRitchie's intro:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

jannet wrote:

Lois Patterson, in her book 'Teach Yourself Excel 97' sets an exercise to
create a Lottery Number selector with a set of six random numbers.
=INT(45*RAND)+1 This is fine and easily done.
It goes on to say that by using the IF function it is possible to prevent
the generated numbers being duplicated in the set. After weeks of
frustration I am unable to solve this problem. Please can anyone help?

--

Dave Peterson

Here is a formula way

First, ensure dell A1 is empty and goto ToolsOptions and on the Calculation
tab check the Iteration checkbox to stop the Circular Reference message.


Next, type this formula into cell B1

=IF(($A$1="")+(AND(B10,COUNTIF($B$1:$B$6,B1)=1)), B1,INT(RAND()*45)+1)


it should show a 0


Copy B1 down to B6.


Finally, put some value in A1, say an 'x', and all the random numbers will
be generated.


They won't change.


To force a re-calculation, clear cell A1, edit cell B1, don't change it,
just edit to reset to 0, copy B1 down to B6, and re-input A1.



--

HTH

RP
(remove nothere from the email address if mailing direct)


"Dave Peterson" wrote in message
...
J.E. McGimpsey has a User Defined function that can work for you:

You can find it he
http://www.mcgimpsey.com/excel/udfs/randint.html

If you're new to macros, you may want to read David McRitchie's intro:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

jannet wrote:

Lois Patterson, in her book 'Teach Yourself Excel 97' sets an exercise
to
create a Lottery Number selector with a set of six random numbers.
=INT(45*RAND)+1 This is fine and easily done.
It goes on to say that by using the IF function it is possible to
prevent
the generated numbers being duplicated in the set. After weeks of
frustration I am unable to solve this problem. Please can anyone help?

--

Dave Peterson

One way would be to create a list of 45 random numbers with the Rand()
function, say in an out-of-the-way area of your sheet.
Then use a formula which positions (ranks) each cell in this list in
numerical order, and then references the cells ranking against it's actual
row placement, thus eliminating the possibility of any duplication, since a
tied result cannot place 2 cells in the same row position.

This is more properly referred to as a "random order" of set values, instead
of *true* random numbers.

In Z1 enter
=RAND()
And copy down to Z45.

Then, enter this formula anywhe

=INDEX(ROW($A$1:$A$45),RANK(Z1,$Z$1:$Z$45))

And copy down for 6 rows to obtain 6 numbers,
Or copy any number of rows to return that amount of numbers.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"jannet" wrote in message
...
Lois Patterson, in her book 'Teach Yourself Excel 97' sets an exercise to
create a Lottery Number selector with a set of six random numbers.
=INT(45*RAND)+1 This is fine and easily done.
It goes on to say that by using the IF function it is possible to prevent
the generated numbers being duplicated in the set. After weeks of
frustration I am unable to solve this problem. Please can anyone help?

Dave O wrote:
Doesn't that contradict the fundamental idea of random numbers?
A truly random number is one that has exactly the same chance of
appearing in a distribution as any other number. By preventing
duplicates you'd be introducing non-randomness.

No. It is called "random sampling without replacement".
It is still random; you are simply reducing the population
with each selection -- just like dealing cards from a
shuffled deck.