Raffle that lets one person have more than one entry.

To:  Danyale
Re:  weighted random name pick

Assuming the 30 names are in range(A1:A30) then the formula:

 =INDEX(A1:A30,RANDBETWEEN(1,30),1)   will pick one name at random.
Tap the F9 key to repeat.
I would place the names in a random order on the worksheet, but it is not necessary.
'---


Special Randoms Excel workbook
Download from MediaFire...

Hi Danyale,

As Dana has mentioned above, it is not necessary to use macros in your situation. You can use LOOKUP as an easier way to achieve it. I have summarized Dana’s solution as below

After you have entered your data in Excel which is Name and Entries in this case.

Step One: Calculate the percentage of weight for each person. In this case, the sum of entries is 30 entries. You can use no. of entries of that person to divide total entries. For example, The percentage of Joe is: 5/30= 0.16667 (B2/30)

Step Two: Calculate the accumulate weight for each person. In this case, Joe will start from zero for his accumulate weight. For Alex, the accumulate weight will be 0 + 0.166667=0.166667

Step Three: Set up random value by key in function: RAND(). RAND() function will help you to randomly generate a number from 0 to 1.

Step Four: set up LOOKUP function: LOOKUP function can help you to find a value from your selected row or column. For example, if the random value is 0.58321, the name of winner is Alex, since the value is between 0.166667 and 0.666666

As the result, once the random value has been generated, the name of winner will show in the box.

Hope this can help you.

Best regards,

Dihao

Hi Danyale,

Thank you for your update.

The percentage weight can help you to determine which people has the better chance to be the winner in this game. For example, Alex purchased 15 tickets among 5 of people which he has the highest percentage 0.5/50% out of 100% to win this game.

Based on the winning percentage we can figure out the accumulate percentage of each people. It can figure out a range of number for each people. If the person has higher percentage to win, the range of number would also be higher. For example, Alex has 0.5/50% to win this game. The accumulate percentage of Alex will be 0.166667. The range of number to let Alex win the game will be 0.166667 to 0.666666.

Therefore, once the RAND () function has generated a number from 0 to 1. A higher rate of the random number will be fall into Alex” range. As the result. He has more opportunities to be the winner of the game.

The limitation for this method is you need to key in entries for each person so that you can calculate percentage rate and accumulate percentage in Excel. If you want to save time, macros is a good way to do it.

Hence, if you decide to use macros, I will move your thread to Office Programming category. A lot of expert advisors could help you to achieve it.

Best regards,

Dihao

Hi Danyale,

Does the explanation of the method clear to you? If you still have any concern, please comeback to here. We will do our best to address it. Thank you.

Best regards,

Dihao