Creating a formula using RAND

H

hbb2699

I have a spreadsheet that has a group of peoples names on it. I want to
be able to create a formula that will cause it to randomly choose one
of those people on the list. I know there is a way to create a formula
for this but I completely clueless on how I would type the formula in.
Can anyone help me with this? Thank you!
 
B

Bob Phillips

=INDEX(A1:A10,INT(RAND()*10)+1)

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)
 
J

joeu2004

hbb2699 said:
I have a spreadsheet that has a group of peoples names on it. I want to
be able to create a formula that will cause it to randomly choose one
of those people on the list. I know there is a way to create a formula
for this but I completely clueless on how I would type the formula in.
Click to expand...

There are many ways to do this. To introduce the concept, the
following might suffice:

=index(A1:A10, int(10*rand()) + 1)

where A1:A10 contains the list of people. The key is that
int(10*rand()) returns a number between 0 and 9.

You will quickly learn the downside of relying on RAND() per se: it is
recalculated every time any cell in the spreadsheed is modified. I
work around that by creating a UDF, myrand(), which simply returns
Rnd(). That is not a perfect solution, but it eliminates 99% of the
recalculations.

Caveat: The above solution does not avoid duplicate results if you use
the formula in multiple cells. If you want unique results (i.e. random
selection without replacement), it can be done.
 
Top