Random Number Generator - Help!

[Guest]

Any and all help is appreciated.

Starting in Cell B7, I have a list of names. In E7 is a
sequential number (starting at 1) that is associated with
each name. The list has the potential to be up to 250
names. Other cells (in column A, C & D) have additional
data associated with each name. A macro enters the data
and increments the number by 1 each time.

I want to select a name at random.

In Cell H19 I have the following formula:
=RAND()*MAX(E7:E256))+1

In Cell H20 I have a lookup formula to display a name:
=VLOOKUP(H19, A7:E256,2,TRUE)

This works pretty well, except when a random number is
generated (with digital fractions) sometimes the same name
is selected. Example: 5.19 would select Name6 and 5.65
would also select Name6.

I hope to select a different name each time a random
number is generated.
Any thoughts?
Thanks
Mike

 

[Peo Sjoblom]

You can use this in H9

=INT(RAND()*(250-1)+1)

it will generate a numbers between 1 and 250,
of course since it is random it can come up more than once..

 

[MTStringer]

Thanks Peo. That works much better.
Mike

-----Original Message-----
You can use this in H9

=INT(RAND()*(250-1)+1)

it will generate a numbers between 1 and 250,
of course since it is random it can come up more than once..

--

Regards,

Peo Sjoblom





.

 

[Arvi Laanemets]

Hi

Create a named range p.e.
NumberOfNames=COUNTIF(Sheet1!$B:$B,"<>")-COUNTIF(Sheet1!$B$1:$B$6,"<>")
Create a named range p.e.
Names=OFFSET(Sheet1!$B$7,,,COUNTIF(Sheet1!$B:$B,"<>")-COUNTIF(Sheet1!$B$1:$B
$6,"<>"),1)

Into H19 enter the formula
=RANDBETWEEN(1,NumberOfNames)
Into H20 enter the formula
=INDEX(Names,H19)

or you can drop the entry in H19 at all
H20=INDEX(Names,RANDBETWEEN(1,NumberOfNames))