Random numbers

  • Thread starter Thread starter Noonenose
  • Start date Start date
N

Noonenose

I want to randomly create a column of 7 numbers between 1 and 47

each cell has to check the others to ensure no two are identical

I do not have any programming expierience and wonder if someone could help
me

Thanks
 
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 A7

=IF(OR(A1=0,B1>1),INT(RAND()*46)+1A1)

Put this formula in B1 and copy down to B7

=COUNTIF(A:A,A1)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Hi,
this work better...:-)...maybe...:-)

=IF(OR(A1=0,B1>1),INT(RAND()*47+1),A1)
=COUNTIF(A:A,A1)

--
Just an idea nothing more.

Jumbo


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 A7

=IF(OR(A1=0,B1>1),INT(RAND()*46)+1A1)

Put this formula in B1 and copy down to B7

=COUNTIF(A:A,A1)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Hi,
if you still get duplicate, you need to set iteration with a setting of 100
or more.
or press the key F9 to force recalculation.

--
Just an idea nothing more.

Jumbo


Thanks this works (almost) :)
I still get dupicate numbers

Bruce
 
Frank Kabel said:
Hi

have a look at the following website (includes also a userdefined
function)
http://www.mcgimpsey.com/excel/randint.html

Frank
Click to expand...

It's not a function but it has been suggested many times before.
Place the integers 1:47(or whatever) in column A
Equate the corresponding cells of B to RAND()
Select the required number of cells in D (say)
Array equate this to A (CNTL/SHIFT/ENTER)

Select column B , shift click A and sort each time you want a new set in D
 
Back
Top