Random number Generator

  • Thread starter Thread starter RayT
  • Start date Start date
R

RayT

Could you kindly assist me with a formula that generate 6 numbers between 1
and 49, without repeating any of the integers.
Thanks!
 
I don't believe there is anyway to do this with a formula; you'll need some
VBA code. See http://www.cpearson.com/Excel/randomNumbers.aspx for quite a
few choices. Look at the section entitled "Getting An Array Of Unique,
Non-Duplicated Value" and the UniqueRandomLongs function.


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2008
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
 
This should work for you:

In an out-of-the-way location, say Column Z, enter the Rand function,
In Z1 enter
=Rand()
And copy down to Z49.

Then enter this formula wherever you wish:

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

Copy down as many rows as you need random numbers.

Each time you hit <F9>, you'll get a new random selection.

You might choose to place your calc mode into Manual, so that you don't
inadvertently refresh the list.
 
One other play using formulas which might interest you ..

Illustrated in this "Ready-to-randomize" sample:
http://www.freefilehosting.net/download/3acjd
Randomize 1 - 49 into a 6 col x 8 row grid.xls

The set-up:
Numbers 1 - 49 to be randomized listed in A1:A49
In B1: =RAND()
In C1: =INDEX($A:$A,RANK(B1,B$1:B$49))
Copy B1:C1 down to C49

Place in say, E2:
=INDEX($C:$C,ROWS($1:1)*6-6+COLUMNS($A:A))
Copy E2 across to J2, fill down to J10. Clear F10:J10.
The grid E2:J9 returns 8 sets of 6 random numbers
from the source in col A. E10 returns the last element.

Press F9 to regenerate.

---
 
Hey Max thats some good stuff there man. Its almost the same as how RagDyer
would do, well explained, u make it sound easy. Thanks mate.
 
RagDyer, that was again well said, it seems quite simply, i guess if you know
your stuff. It liked it. Thank u all.
 
Using VBA is very interesting, i really would love to learn alot about that.
Enjoyed working through the steps in the example from the page you mentioned.
I sure would recommend anyone who loves to learn more to try it. I
personally enjoyed it. Guez there are many ways to skin a cat. Cheers!
 
In an out-of-the-way location, say Column Z, enter the Rand
function[.] In Z1 enter =Rand()[.] And copy down to Z49.

Then enter this formula wherever you wish:
=INDEX(ROW($A$1:$A$49),RANK(Z1,$Z$1:$Z$49))
Copy down as many rows as you need random numbers.
Click to expand...

Can you please explain the theory of operation.

I agree that it works. But it seems that =RANK(Z1,$Z$1:$Z$49) would
suffice. RANK(Z1,$Z$1:$Z$49) seems to return the same thing that the
full INDEX expression returns. It seems that ROW($A$1:$A$49) is
always 1.

In any case, I agree with RayT that this is a clever, yet nicely
simple solution to the problem.
 
In an out-of-the-way location, say Column Z, enter the Rand function[.]
In Z1 enter =Rand()[.] And copy down to Z49.

Then enter this formula wherever you wish:
=INDEX(ROW($A$1:$A$49),RANK(Z1,$Z$1:$Z$49))
Copy down as many rows as you need random numbers.
Click to expand...

Can you please explain the theory of operation.

I agree that it works. But it seems that =RANK(Z1,$Z$1:$Z$49) would
suffice. RANK(Z1,$Z$1:$Z$49) seems to return the same thing that the
full INDEX expression returns. It seems that ROW($A$1:$A$49) is
always 1.

In any case, I agree with RayT that this is a clever, yet nicely
simple solution to the problem.
 
In an out-of-the-way location, say Column Z, enter the Rand function[.]
In Z1 enter =Rand()[.] And copy down to Z49.
Then enter this formula wherever you wish:
=INDEX(ROW($A$1:$A$49),RANK(Z1,$Z$1:$Z$49))
Copy down as many rows as you need random numbers.
Click to expand...
[....]
I agree that it works. But it seems that =RANK(Z1,$Z$1:$Z$49) would
suffice. RANK(Z1,$Z$1:$Z$49) seems to return the same thing that the
full INDEX expression returns. It seems that ROW($A$1:$A$49) is
always 1.
Click to expand...

Only out of context. ROW($A$1:$A$49) returns the array {1,2,...,49}.
In the context of INDEX(), the RANK() result (1,2,...,49) is used to
index into that array.

In this context, I believe that using INDEX() and ROW() is redundant,
since the OP is interested in randomly choosing amount 1,2,...,49,
which is exactly what RANK() returns, given that Z1:Z49 contains
random values.

However, if the OP had been interested in, for example, randomly
selecting from the range 13-61, then perhaps INDEX(ROW($A$13:$A
$61),RANK(Z1,$Z$1:$Z$49)) could be used -- although, I believe that
12+RANK(Z1,$Z$1:$Z$49) would suffice.

On the other hand, if the OP had wanted to randomly select unique
values from A1:A49, then I believe INDEX($A$1:$A$49,RANK(Z1,$Z$1:$Z
$49)) could be used, copying down for as many selections as required.

RagDyer (or any other expert), please comment.
 
In this particular case, you're right, the Rand() function would suffice.

However, I always suggest the combination with Index() as a more or less
*generic* solution, which can be understood and built on.

What if the OP *later* wanted numbers *other* then 1 to 49, say 10 to 58, or
100 to 148?

=INDEX(ROW($A$100:$A$148),RANK(Z1,$Z$1:$Z$49))

OR, say that the list of numbers are *not consecutive*, OR, say that a
random list of names is desired ... with the master list located at say J50
to J98:

=INDEX($J$50:$J$98,RANK(Z1,$Z$1:$Z$49))

So, you can see how easily a revision might be accomplished if the necessary
basic functions are presented at the outset.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------


In an out-of-the-way location, say Column Z, enter the Rand
function[.] In Z1 enter =Rand()[.] And copy down to Z49.

Then enter this formula wherever you wish:
=INDEX(ROW($A$1:$A$49),RANK(Z1,$Z$1:$Z$49))
Copy down as many rows as you need random numbers.
Click to expand...

Can you please explain the theory of operation.

I agree that it works. But it seems that =RANK(Z1,$Z$1:$Z$49) would
suffice. RANK(Z1,$Z$1:$Z$49) seems to return the same thing that the
full INDEX expression returns. It seems that ROW($A$1:$A$49) is
always 1.

In any case, I agree with RayT that this is a clever, yet nicely
simple solution to the problem.
 
Sorry, just detected that the earlier sample contained an inadvertent error*

Here's the corrected version:
http://www.freefilehosting.net/download/3adkm
Randomize 1 - 49 into a 6 col x 8 row grid.xls

*The formulas in the output grid E2:J10 got messed up. To correct, just
re-copy E2 across/down to J10. Clear F10:J10. The grid E2:J9 will return 8
sets of 6 random numbers from the source in col A. E10 returns the last
element.

---
 
Back
Top