Random numbers

[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

 

[Bob Phillips]

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)

 

[Jumbo]

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)

 

[Frank Kabel]

Hi

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

Frank

 

[Noonenose]

Thanks this works (almost)
I still get dupicate numbers

Bruce

 

[Jumbo]

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

 

[James Silverton]

Hi

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

Frank

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