Home » Forums » Open Source Learning Center » Question and Answer

Random Drawing for a Bicycle from a list

Submitted by bandmom721 on 6 December, 2012 - 21:00

In excel, I have a list of names and the column next to it is the number of times the person gets their name in a drawing, (ex. John Doe, 15). How can I get from the list to the winner without having to type out all the names and randomizing them?

‹ Default formal if no data. Automating allotment ›
»

Exactly what I needed

Submitted by bandmom721 on 9 December, 2012 - 23:37.

AndyLitch,

Yes, that is exactly what I had and what I needed. It worked wonderfully. I just could not wrap my mind in the right direction last week I guess.

Thanks....

»

If I understand

Submitted by AndyLitch on 7 December, 2012 - 10:54.

If I understand correctly...

1. We are talking about a raffle
2. The number column represents the number of tickets the customer has bought
3. You want to automate the prize draw fairly and simply using Excel ..

»

If I'm correct then this

Submitted by AndyLitch on 7 December, 2012 - 11:15.

If I'm correct then this should work

Assuming that :-
The list of names runs from cell A11 downwards
The corresponding number of tickets bought runs from cell B11 downwards

Steps to randomise the winner
1. Distribute tickets evenly
1a. In cell B7 ..... =SUM(B$11:B$10000)
1b. in cell C11...... =D10+1
1c. In cell D11...... =MROUND(((B11/$B$7)*$B$7)+D10,1)
1d. Paste the functions in C11:D11 down through the list
These columns now represent unique blocks of ticket numbers (from - to) for each customer

2 Calculate the draw and select the winner
2a. In cell G3..... =RANDBETWEEN(1,$B$7) ... generates a number between 1 and the number of tickets sold
2b. In cell G5..... =MATCH($G$3,D11:D62)
2c. In cell G6..... =OFFSET(A10,G5+1,0,1,1) Winning Name
2d. In cell H6..... =OFFSET(C10,G5+1,0,1,1) Winners lower ticket number
2e. In cell I6..... =OFFSET(D10,G5+1,0,1,1) Winners upper ticket number

»