Creating a formula using RAND

[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 got the formula in the spreadsheet and it randomly picks a person, but everytime I hit enter it chooses someone else. Is there a way I can make it only recalculate if I hit F9. The formula I am using is
=INDEX(A3:A42,RAND()*ROWS(A3:A42)+1)
I got the above formula from someone else here and since I am fairly new to creating formulas I am not sure how to set it up so it doesn't automatically choose someone else when I hit enter. Can anyone help me with this? Thank you!

[Bob Phillips]

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

--

HTH

Bob Phillips

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

"hbb2699" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[[email protected]]

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.