random selection from a range of cells

[tjb]

I'm making a prize drawing list.

I have a list of about 8500 numbers, some of which are duplicated, which is
OK, I want multiple entries to increase chance of winning. How can I return
a random number at will from this range of cells?

 

[Bernie Deitrick]

tjb,

Each time you recalc (Ctrl-Alt-F9), this will return a random value from
column A, no matter how many values you have in column A. Note that you
cannot have any blanks within your list, and your list must start in cell
A1.

=INDEX(A:A,ROUND(RAND()*COUNTA(A:A),0))

HTH,
Bernie
MS Excel MVP