Formula to run a weighted random draw

[Steve_Long]

Would someone be able to help me with a formula to randomly pick a winner accounting for the number of times they are “entered” in a draw. My table is tracking exercises where students are awarded an “entry” in the draw for each workout or challenge they complete. Example table is:
Bob = 8
George = 12
Suzie = 22
Nigel = 11
The number represents how many chances they should have to win.
Is there a formula that will account for the weighting and randomly choose my winner?
Thanks for any help!
Steve

[JonathanBowen]

Hi @Steve_Long - don’t think you can do this with a formula, but you can do it with the scripting block. My method is to create an array which has the names represented as many times as each person has made entries to the draw. So if my table looks something like this:

then I end up with an array of names like this:

["Jim", "Jane", "Jane", "John", "John", "John", "Julie", "Julie", "Julie", "Julie"]

Then, using a random number generator, I select the element of the array given by the random number.

Here is the script:

// define the table
let table = base.getTable("Table 1");

// get the records of entries
let query = await table.selectRecordsAsync();

// create an empty array
let entries = [];

// looping through the records...
for (let record of query.records) {
    let name = record.getCellValue("Name");
    let numberOfEntries = record.getCellValue("Entries");
    // push the name from each record into the array
    // as many times as they have made entries to the draw
    for (let i = 0; i < numberOfEntries; i++) {
        entries.push(name);
    }
}
// at this point entries will look something like this:
// ["Jim", "Jane", "Jane", "John", "John", "John", "Julie", "Julie", "Julie", "Julie"]

// get the total length of the array (the total number of entries to the draw)
let totalEntries = entries.length;
// generate a random number between 0 and (totalEntries - 1)
let randomNumber = Math.floor(Math.random() * totalEntries);
// display the winner - the element of the array at index(randomNumber)
output.text(`The winner is: ${entries[randomNumber]}`);

I think this method is sound, but happy for probability experts out there to challenge on this one :slightly_smiling_face:

To test it out I ran a modified version of the script which looped through the process 500 times and saved the winner each time to another table. The results were:

Based on this, if I ran it for 1000 iterations or 5000 iterations, we should get closer and closer to the expected results.

JB

[Steve_Long]

Thanks for your time on this Jonathan. From what I’ve googled, I believe that’s the correct way to weight the entries. I’ll have to spend some time learning to use the scripting block, but understood your code well enough. Thanks! I’ll post back once I get it running.

Steve