Forum Discussion

jberlin's avatar
jberlin
Copper Contributor
Nov 02, 2020
Solved

Formula for choosing a winner (weighted)

Hello - I have tasked with choosing a winner for a contest that was run by my company. A contestant has increased odds of winning based on the employees sales total. ie: $1,000 raised equals one ...
  • mtarler's avatar
    mtarler
    Nov 02, 2020

    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.

     

Resources