SOLVED

Formula for choosing a winner (weighted)

Copper Contributor

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 entry
  • $2,500 raised equals two entries
  • for every additional $2,500 raised, 1 additional entry is given

Does anyone have any formula suggestions to serve as a solution to randomly picking a winner based on the above criteria? Thank you in advanced! 

9 Replies

@jberlin 

 

https://www.random.org/

 

Alternative info:

https://techcommunity.microsoft.com/t5/excel/random-selection-for-raffle-winners/m-p/1748954#M77039

 

Hope I was able to help you.

 

Nikolino

I know I don't know anything (Socrates)

 

* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here

 

 

@jberlin I am confident that somewhere in the reference given by @NikolinoDE  is a solution but I couldn't tell you which one.  That said, I assume you have a spreadsheet with everyone's sales numbers and would suggest you create a couple of helper columns to help with transparency.  The 1st would be # entries and the second is cumulative entries.  The equation for the @# entries would be:

= 1 + INT([SALES]/2500)

the second column would be 

= [Prev Row] + [Num Entries]

So if the first helper column is in column D then E2 would be = E1 + D2 and fill down 

Now for actually finding the winner you need to Generate a Random number.  Usually a RAND() function like excel creates a number between 0 and 1 so multiply by the total number of entries (last or max value in the cumulative column) then scroll down to the winner or use VLOOKUP() or MATCH() to find the winner.

That said, how you find the RAND() and document it is important for transparency.  When you are ready to 'draw' the winner you may want to have it witnessed or video taped or something.  If you just put the formula =RAND() * MAX(E:E) it will give you that value BUT it will keep changing every time you change ANY other cell and hence you need to somehow decide how to define which time 'COUNTS' for the real drawing.

You could also write a simple Macro to generate a random number that could be triggered, but you should still document it since you could generate it as many times as you want.

Hope this was of some help

 

 

@mtarler  I am so grateful for your prompt reply and assistance on this project, thank you!!

 

In terms of assigning the number of entries, the =1+INT(Sales Total/2500) formula works wonderfully!

 

Though with my current use of the RAND formula, I'm not sure if if I'm capturing the the use of the number of entries column/ if I am giving those with a higher number in that column, a higher likelihood to win the challenge. This is what I currently have =INT(RAND()*Total Cumulative).  

 

I have attached a sample document. 

 

Thank you in advanced!! 

best response confirmed by jberlin (Copper Contributor)
Solution

@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 wonderful!! Thank you SO much!!

@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.
1 best response

Accepted Solutions
best response confirmed by jberlin (Copper Contributor)
Solution

@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.

 

View solution in original post