Forum Discussion
Need reccomendations for building a ticket puller for a raffle with Excel.
I have very little understanding in excel so I will go through what i have tried so far.
I need my sheet to output 3 different names weighted by ticket amount. I have tried using a =lookup combined with a =rand() but it did not quite work like I had wanted. The function I was attempting to use to select winners is =LOOKUP(J3,G4:G9,B4:B9) where the G cells are the Cumulative Weight, J3 is a =RAND() output and B cells are the name of the winner. I have 2 very critical issues.
1) The way I have done this Cory has a much, much higher likelihood to win in the example table given the way that =RAND() functions and the lookup works with the Cumulative Weight and Garret would never be pulled no matter how many tickets he has given that his cumulative total would always be 1.
2) I would prefer a way to have a name drawn only once from the table but I can also just do that manually if I can solve issues and actually get the table to function correctly.
I do not have a good enough grasp of Excel to know how to fix this and make the table function as intended. Below is a sample of what my data table is currently outputting with the sample inputs.
| Ticket Holder | Donation Amount | Rate 500-1 | Ticket Total:544 | Weight% | Cumulative Weight | Winners | 0.70157 | 0.27363 | 0.84860 | |
| Luke | 2000 | 4 | 0.007353 | 0.007353 | Grand Prize! | |||||
| Jack | 20000 | 40 | 0.073529 | 0.080882 | Cory | |||||
| Sarah | 10000 | 20 | 0.036765 | 0.117647 | Second Prize! | |||||
| Cory | 30000 | 60 | 0.110294 | 0.227941 | Cory | |||||
| Laura | 200000 | 400 | 0.735294 | 0.963235 | Third Prize! | |||||
| Garret | 10000 | 20 | 0.036765 | 1.000000 | Cory |
Joshua655 You were very close, instead of LOOKUP, it's better to use XLOOKUP where we can define the match mode to be "Next larger". Your formula should look like this:
=XLOOKUP(RAND(),E2:E7,A2:A7,,1)To make sure each participant can only win one prize, we have to filter the existing dataset as following:
=FILTER(A2:C7,A2:A7<>G2)I made a custom video for you, explaining how to build the raffle system step-by-step: https://www.youtube.com/watch?v=gjT_92KZy-E
Optional tip if this was helpful: [link removed by admin]
1 Reply
Joshua655 You were very close, instead of LOOKUP, it's better to use XLOOKUP where we can define the match mode to be "Next larger". Your formula should look like this:
=XLOOKUP(RAND(),E2:E7,A2:A7,,1)To make sure each participant can only win one prize, we have to filter the existing dataset as following:
=FILTER(A2:C7,A2:A7<>G2)I made a custom video for you, explaining how to build the raffle system step-by-step: https://www.youtube.com/watch?v=gjT_92KZy-E
Optional tip if this was helpful: [link removed by admin]