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
|
Similar Threads
