Random Numbers

  • Thread starter Thread starter jannet
  • Start date Start date
J

jannet

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?
 
Here is a formula way

First, ensure dell A1 is empty and goto Tools>Options 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(B1>0,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)
 
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 anywhere:

=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.
 
Dave said:
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.
Click to expand...

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.
 
Back
Top