randomly select a cell from an array

[Guest]

If I have a table of values, is there a way to randomly select one of the
cell's values?
For example, I have a table of ticket numbers. They are not in any order and
they are not all inclusive. How can I randomly choose on of the ticket
numbers to be a winning ticket?

 

[Ragdyer]

Say your list of ticket numbers is in A1 to A20.

Try this formula:

=INDEX(A1:A20,INT(RAND()*20)+1)

Each time you hit <F9>, you'll get a new random pick.

 

[Guest]

My numbers are in A1 to G120, I will try to modify this formula. Thanks

 

[Guest]

=ADDRESS(INT(RAND()*(120-1)+1),INT(RAND()*(7-1)+1))
That will give you the winning address, which might make finding it easier.
Assume that's in H1. In I1 you could put =INDIRECT(H1) to display the value.

Or if you want to just see the winning number:
=INDIRECT(ADDRESS(INT(RAND()*(120-1)+1),INT(RAND()*(7-1)+1)))

If you have Excel 2007, you could use RANDBETWEEN() instead of RAND() and it
is a little easier to understand.

As with Ragryder's solution, pressing F9 (or making a change on the sheet)
will result in a new number being 'drawn'.
Be sure you write it down, because it is pretty easily changed.

 

[Guest]

SWEET!!! This is perfect!!! TYTYTYTYTYTY

=ADDRESS(INT(RAND()*(120-1)+1),INT(RAND()*(7-1)+1))
That will give you the winning address, which might make finding it easier.
Assume that's in H1. In I1 you could put =INDIRECT(H1) to display the value.

Or if you want to just see the winning number:
=INDIRECT(ADDRESS(INT(RAND()*(120-1)+1),INT(RAND()*(7-1)+1)))

If you have Excel 2007, you could use RANDBETWEEN() instead of RAND() and it
is a little easier to understand.

As with Ragryder's solution, pressing F9 (or making a change on the sheet)
will result in a new number being 'drawn'.
Be sure you write it down, because it is pretty easily changed.

 

[Guest]

Well, I had an advantage over Ragryder - by the time I got here, I knew you
had a rectangular matrix and what range it covered. If he'd had that info,
he'd have come up with pretty much the same thing early on.

 

[Guest]

Arrrrggggg.... Ragdyer!!!!! not Ragryder. Apologies to the big R.