This is a tricky one...

I have collected a set of data regarding the performance of many employees.
Certain tasks at work allow employees to collect 'tickets'. I have a data
capture form into which the line managers enter how many 'tickets' each
employee has earned in the week. By the end of each month I have a list
which contains just 2 columns. Col A is a list of names and Col B is the
number of tickets earned. I need a way of replicating a raffle draw based on
these values. Each month there may be a different number of prizes available
to be won.

If john has 30 tickets, jim has 100 tickets and clare has 70 tickets how can
I work out a random way of deciding who has won each prize? This is
difficult to explain in text but just because someone has 50 per cent of the
tickets does not mean that they should win 50 per cent of the prizes.

I guess that at the minute I am asking you clever people to help me with the
logic of this as much as I am asking for the code...

Any help you are able to offer will be greatly appreciated.

Thanks

This is a very simple problem whan you add a third column which is the total
number of tickets sold. for example

Person tickets total tickets
Moe 20 20
Larry 30 50
Curly 40 90
Howard 20 110
Shemp 25 135
Joe 10 145
Joe Becker 5 150

If you choose a random number between 0 to 150 then

if the number is 0 <= X < 20 Moe wins
if the numbe is 20 <= X < 50 Larry wins

Rand() produces a number between 0 and 1. So you then justt multiply by 150
and look up the number in the 3rd column. The lookup can then be done with
Vlookup. Use True as the match type which returns the largest value less
than or equal to the search number.


"pablo bellissimo" wrote:

This is a tricky one...

I have collected a set of data regarding the performance of many employees.
Certain tasks at work allow employees to collect 'tickets'. I have a data
capture form into which the line managers enter how many 'tickets' each
employee has earned in the week. By the end of each month I have a list
which contains just 2 columns. Col A is a list of names and Col B is the
number of tickets earned. I need a way of replicating a raffle draw based on
these values. Each month there may be a different number of prizes available
to be won.

If john has 30 tickets, jim has 100 tickets and clare has 70 tickets how can
I work out a random way of deciding who has won each prize? This is
difficult to explain in text but just because someone has 50 per cent of the
tickets does not mean that they should win 50 per cent of the prizes.

I guess that at the minute I am asking you clever people to help me with the
logic of this as much as I am asking for the code...

Any help you are able to offer will be greatly appreciated.

Thanks

Thanks for you reply Joel but I'm still having problems.

I used your logic to generate 1000 random draws and looked up the name in
the way you suggested. After recalculating the sheet approx 50 times, Larry
consistently won more ticket draws than any other and Joe Becker never won
any from approx 50000 tickets drawn.

I came up with another method which is a bit messy to automate but seems to
work. Could you just check out my logic.

I have written code which creates a list containing each name numerous times
(each name appears once for each ticket). I then generate a RAND() in the
column next to the list and sort by these values. When I next do a single
RANDBETWEEN(1,150) this gives me the row number where I can find the winning
team members name. How does this sound to you??

The next problem I have is to automate this so that from the original list a
user can just enter how many prizes there are (in an input box) and they will
be given this number of random names from the list. I'll let you know if I
get stuck with any specific bits of the code for this.

Thanks Again!

"Joel" wrote:

This is a very simple problem whan you add a third column which is the total
number of tickets sold. for example

Person tickets total tickets
Moe 20 20
Larry 30 50
Curly 40 90
Howard 20 110
Shemp 25 135
Joe 10 145
Joe Becker 5 150

If you choose a random number between 0 to 150 then

if the number is 0 <= X < 20 Moe wins
if the numbe is 20 <= X < 50 Larry wins

Rand() produces a number between 0 and 1. So you then justt multiply by 150
and look up the number in the 3rd column. The lookup can then be done with
Vlookup. Use True as the match type which returns the largest value less
than or equal to the search number.


"pablo bellissimo" wrote:

This is a tricky one...

I have collected a set of data regarding the performance of many employees.
Certain tasks at work allow employees to collect 'tickets'. I have a data
capture form into which the line managers enter how many 'tickets' each
employee has earned in the week. By the end of each month I have a list
which contains just 2 columns. Col A is a list of names and Col B is the
number of tickets earned. I need a way of replicating a raffle draw based on
these values. Each month there may be a different number of prizes available
to be won.

If john has 30 tickets, jim has 100 tickets and clare has 70 tickets how can
I work out a random way of deciding who has won each prize? This is
difficult to explain in text but just because someone has 50 per cent of the
tickets does not mean that they should win 50 per cent of the prizes.

I guess that at the minute I am asking you clever people to help me with the
logic of this as much as I am asking for the code...

Any help you are able to offer will be greatly appreciated.

Thanks

Both methods do work. It is just less programming to do it my way. Also
with my method you don't need tto write any VBA code, it can all be done in a
spreadsheet.

Run a test for 50 trials, Joe Becker will only win (5/150) * 50 = 1.66
times. 50 trials may not be enough to see good results. The results you get
depends on how random your random number generator actually is. Some
generators are better than others.

"pablo bellissimo" wrote:

Thanks for you reply Joel but I'm still having problems.

I used your logic to generate 1000 random draws and looked up the name in
the way you suggested. After recalculating the sheet approx 50 times, Larry
consistently won more ticket draws than any other and Joe Becker never won
any from approx 50000 tickets drawn.

I came up with another method which is a bit messy to automate but seems to
work. Could you just check out my logic.

I have written code which creates a list containing each name numerous times
(each name appears once for each ticket). I then generate a RAND() in the
column next to the list and sort by these values. When I next do a single
RANDBETWEEN(1,150) this gives me the row number where I can find the winning
team members name. How does this sound to you??

The next problem I have is to automate this so that from the original list a
user can just enter how many prizes there are (in an input box) and they will
be given this number of random names from the list. I'll let you know if I
get stuck with any specific bits of the code for this.

Thanks Again!

"Joel" wrote:

This is a very simple problem whan you add a third column which is the total
number of tickets sold. for example

Person tickets total tickets
Moe 20 20
Larry 30 50
Curly 40 90
Howard 20 110
Shemp 25 135
Joe 10 145
Joe Becker 5 150

If you choose a random number between 0 to 150 then

if the number is 0 <= X < 20 Moe wins
if the numbe is 20 <= X < 50 Larry wins

Rand() produces a number between 0 and 1. So you then justt multiply by 150
and look up the number in the 3rd column. The lookup can then be done with
Vlookup. Use True as the match type which returns the largest value less
than or equal to the search number.


"pablo bellissimo" wrote:

This is a tricky one...

I have collected a set of data regarding the performance of many employees.
Certain tasks at work allow employees to collect 'tickets'. I have a data
capture form into which the line managers enter how many 'tickets' each
employee has earned in the week. By the end of each month I have a list
which contains just 2 columns. Col A is a list of names and Col B is the
number of tickets earned. I need a way of replicating a raffle draw based on
these values. Each month there may be a different number of prizes available
to be won.

If john has 30 tickets, jim has 100 tickets and clare has 70 tickets how can
I work out a random way of deciding who has won each prize? This is
difficult to explain in text but just because someone has 50 per cent of the
tickets does not mean that they should win 50 per cent of the prizes.

I guess that at the minute I am asking you clever people to help me with the
logic of this as much as I am asking for the code...

Any help you are able to offer will be greatly appreciated.

Thanks