Random number

E

ed

How would i set up a macro to generate six random numbers
in consecutive cells between 1 and 50. There cannot be a
any duplicate numbers though?

Thanks in advance for your help.

Ed
 
J

Jerry W. Lewis

The usual way to avoid duplicates is to use two columns: one column
containing the numbers 1 ... 50 and the second column containing random
numbers. Sort both columns based on the second column, and take the
first six numbers from the first column.

Jerry
 
B

Bob Phillips

Ed,

Here's one way.

Goto Tools>Options and on the Calculation tab click the Iteration checkbox
(this will suppress circular reference messages)
Put this formula in A1 and copy down to A50
=IF(OR(A1=0,B1>1),INT(RAND()*100),A1)
Put this formula in B1 and copy down to B50
=COUNTIF(A:A,A1)

Change the RAND function to your max values, but make sure it is large
enough to allow unique numbers (90 was the lowest I could get)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
D

Dave B

You could also try this fairly simple method:-

In A1 type =RAND()
Copy this to A2:A50

In B1 type =RANK(A1,a$1:a$50)
Copy this to B2:B6

You will be *ranking* each of the random numbers in A1 to A6 as they appear
within the group A1 to A50 and showing this ranking as an integer in B1:B6

In theory this method could yield duplicates but probably not - given the
small quantity of numbers involved i.e. 6 from 50.

HTH
Dave
 
T

Tom Ogilvy

In theory, it won't produce duplicates since the period of the RAND
algorithm is well in excess of 50 and numbers do not repeat within the
period.
 
Top