Random number

  • Thread starter Thread starter ed
  • Start date Start date
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
 
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
 
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)
 
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
 
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.
 
Back
Top