Hello,

I am running an excel based Jeopardy game and need a way to randomly choose three winners based on their number of entries gained throughout the game. I need a code which is able to take in columns of information (example below) and randomly pick three student numbers, taking into account different weights based on the total number of entries per student.

Student Number/ Automatic Entries / Jeopardy Entries / Total # of Entries
1 / 2 / 2 / 4
2 / 2 / 3 / 5
3 / 2 / 1 / 3
4 / 2 / 1 / 3
5 / 2 / 6 / 8

Obviously, I want the code to reflect the fact that student 5 has more entries than any other student, so on a weighted random number, they should be picked most preferentially. I have attached a file with the example data which will be generated from a game of Jeopardy. Each student present receives an automatic 2 entries, and additional entries are gained by correctly answering questions throughout the game.

One point that you will notice from the file is that there can be anywhere between 1 and 130 students in the game (depending on attendance). The code needs to be able to adjust itself to account for the number of students that will be playing. In the attached file, you can see that only 15 students played the game. I want to be able to tell the program what this total number of playing students is so that a random student number of 33 isn't picked when only 32 students were played the game.

I also don't want the program to randomly pick the same student twice. I want to give out the three prizes to three separate students.

I have never attempted VBU before, and I wasted some time yesterday trying to figure this out on my own. I have seen that one route to do this is to create an array with a variable number of elements. After I tell the program the total number of students that played, it can calculate the total number of entries. After creating that number of blanks in the array, it can then just fill in the blanks with the appropriate student numbers. Then, it can randomly pick three index numbers, extract the student numbers (making sure that they are three different ones), and then report those three numbers.

An explanation in the answer of how to actually incorporate this function, how to direct the function to the needed input data, and have it display these three winning student numbers would also be much appreciated. At this point I have no idea how to get a function to export these three numbers to three cells in the workbook.

Any help will be greatly appreciated!

Data File Example.xlsx

Ben