If the list of names starts in cell A2 and if the Y's and N's are in
columns B:E enter this formula in cell F2:
=SUMPRODUCT(--(B2:E2="Y"))

in cell F3 copy the following formula and copy this cell down as far as
needed:
=SUMPRODUCT(--(B3:E3="Y"))+F2

in cell H2 enter the following formula:
=ROUNDUP(RAND()*(ROUNDUP(MAX(F:F),0)),0)

In G1 enter:
=G2

in cell G2 copy the following formula and copy this cell down as far as
needed:
=IF($H$2<=F2,A2,G3)

G1 will return the winner everytime the random generator runs.

Hope that makes sense.

-Ikaabod.


Fleone Wrote:
Gary's Student, Niek,
Thanks for your very quick responses. Both of these suggestions would
work,
but aren't exactly what I am looking for. I am hoping to not have to do
any
continual editing of the data. If at all possible, I would like to be
able to
generate the number of instances that a particular name might appear in
the
list automatically.
For example, last week Adam had only one entry, but this week because
he did
so well, he got 3. Instead of having to change the formula, or
repopulating a
list by hand, I would like to have it automated. Here are some more
details.
----
Adam Y Y N N 2
Bob Y Y Y Y 4
Charlie Y N Y Y 3
The cells containing Y or N will change on a weekly basis, that change
will
then alter the numbers appearing the last column. The numbers in the
last
column will directly indicate the number of entries that each person
would
have and will update automatically when the Y/N cells are changed. I
would
like to be able to give each person a "chance" of being picked based on
the
number of Y's that they have against the total number of Y's available.
So
something like RANDBETWEEN(1,9) but giving Adam two chances of being
picked,
Bob 4, and Charlie 3.
Does this help at all?


"Fleone" wrote:

Hi everyone. I am trying to figure out a way to make a spreadsheet
version of
pulling a name out of a hat.
I have a list of names, let's say Adam, Bob, and Charlie.
Adam has one slip of paper with his name on it, Bob has two, and
Charlie has
three.
Adam 1
Bob 2
Charlie 3
I want to be able to maybe select a command button, or hit F9 to
refresh the
page and have a random selection from the list appear based on the
number of
entries that each person has. If my thinking is correct Charlie would
be more
likely to have his name drawn than either Adam or Bob although the
drawing
itself is random.
Thanks a bunch for any help.


--
Ikaabod
------------------------------------------------------------------------
Ikaabod's Profile: http://www.excelforum.com/member.php...o&userid=33371
View this thread: http://www.excelforum.com/showthread...hreadid=536131