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!


--
hbb2699
------------------------------------------------------------------------
hbb2699's Profile: http://www.excelforum.com/member.php...o&userid=35224
View this thread: http://www.excelforum.com/showthread...hreadid=552313

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

--

HTH

Bob Phillips

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

"hbb2699" wrote in
message ...

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!


--
hbb2699
------------------------------------------------------------------------
hbb2699's Profile:
http://www.excelforum.com/member.php...o&userid=35224
View this thread: http://www.excelforum.com/showthread...hreadid=552313

hbb2699 wrote:
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.

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.