Weighted Random Number Generator Code Needed

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

Hi Tiger Avatar,

Thanks for your quick response! I will enter the total number of students playing into cell K4 on worksheet "Entry Summary" to tell the program how many students are playing. This is so that (in regard to the attached example data) the program will know that student 16 was present also, and will also have a chance to win one of the three prizes. Students 11 and 12 were also present but just didn't get a chance to answer a question. I have another random number generator in the program which will use this K4 entry to randomly pick students to attempt a question; however, I want all students present to have a chance to win one of the three prizes. Thanks!

Ben

Hi Tiger Avatar,

I have uploaded a new example data file to give you a better idea of the exact layout of the workbook. In my real file there are many other worksheets which are sending information into these two tables on the worksheet "Entry Summary". The top table is just to summarize which students answered which questions. I am hoping for a macro that will take information from rows 23 and down (depending on the number of students playing entered into cell K4) and the columns in the Student Information Table. I would then like the three randomly picked student numbers to be presented in cells I7, I10, and I13. Does this clarify more?

Updated Data File Example.xlsx

Thanks!

Ben

Hi Tiger Avatar,

Wow! That worked perfectly! Thank you so much! I can't believe that that much code was needed simply to pick a few numbers. Thanks again, I would have never been able to put that together. Also, thanks for the comments throughout the code so that I could follow along and figure out what each line does.

Ben