Forum Discussion
Formula for choosing a winner (weighted)
jberlin OK I tweaked the equation to be =INT(RAND()*D89)+1 because the RAND() function is >=0 but <1 so taking the INT rounds down but the entry numbers start at 1 and since it never equals 1 the 'last' entry would never get selected, so I just +1 to shift it accordingly.
So that is the winning entry # (think off cumulative entry column as showing which ticket number(s) each person was given)
So I added a XLOOKUP function to find who owns that 'ticket #'
Now I also added a *I2 to the RAND() function so now the winning draw doesn't happen until you change I2 to a value of 1. So you can open the sheet and play with it and make sure it works and what not. Then when you are ready for the 'official drawing' you set I2 to 0 and then get the video/witnesses all ready, change it to 1 and kaboom, it draws your winner.
hope that helps.
jberlin OK I tweaked the equation to be =INT(RAND()*D89)+1 because the RAND() function is >=0 but <1 so taking the INT rounds down but the entry numbers start at 1 and since it never equals 1 the 'last' entry would never get selected, so I just +1 to shift it accordingly.
So that is the winning entry # (think off cumulative entry column as showing which ticket number(s) each person was given)
So I added a XLOOKUP function to find who owns that 'ticket #'
Now I also added a *I2 to the RAND() function so now the winning draw doesn't happen until you change I2 to a value of 1. So you can open the sheet and play with it and make sure it works and what not. Then when you are ready for the 'official drawing' you set I2 to 0 and then get the video/witnesses all ready, change it to 1 and kaboom, it draws your winner.
hope that helps.
mtarler This still doesn't take into account the probability of winning based on the number of entries
- VitaliyM Actually it does. The cumulative column adds in how many entries that line gets so if you have 1 entry the number on that first line is 1 but then I have 4 entries the number on my line is 4+1 = 5 so if the random number 1-5 is 1 you win but numbers 2, 3, 4, or 5 come up then I win 🙂 So you have 1 entry and I have 4 entries.
mtarler does it account for 0 entries? I"m doing something similar and sometimes my employees have 0 entries for the monthly draw. Will it affect the people who have entries?
- yes if they have 0 entries the cumulative column adds 0. I would have to double check the vlookup but it should find the 1st value>= the random drawn number and hence if you have:
joe 1 ---- 1
sue 3 ---- 4
dave 0 --- 4
john 3 --- 7
and you draw 4 then Sue wins because her '3 entries' would be 2, 3, 4 and it wouldn't ever "find" dave since it would find Sue first.
