Forum Discussion
Formula help: randomly assign students to groups based in criteria
Hi Tkelly705 ,
See attached example of a solution that may help you with this.
The formula in column F counts how many students have been assigned to the current student's first choice, and if that is less than the max, he is assigned to his first option, if not we move on to check the second option, etc.
Regards,
Zach
Tkelly705 I know you already have a solution but I thought this was a real interesting challenge. So in the attached I created a complex set of LAMBDA functions include 2 that are recursively calling themselves to solve this challenge. In the attached workbook is an example:
The inputs to the main LAMBDA call include the RANGE with the selection table (must be 1 column as a unique ID followed by any number of picks) and a Table with the list of options and corresponding max # spots for that option.
This solution will:
A) randomize the order of the table
B) go down the 1st choices and allow up to the max # for each option
C) continue with each next lower choice columns
D) For EACH row IF that row did not match (i.e. all selected options were already max) then it will auto assign a non-full option based on which ever option has most spots open first. (an example of this is the last line in the image above where E was given even though they didn't select E as an preferred option)
E) Undo the randomized order so that the result correctly lines back up
F) I added in an addition output column to show the 'randomized' order that was used during the process (i found this helpful just for my sake to understand why/how each row ended up getting the choice they got)
NOTE: I didn't test for error cases like duplicate or missing entries and such so I hope you don't have any.
FYI: another interesting challenge if you guys are interested. I probably way over complicated it.
Since you seem to like a challenge, here's my related conundrum.
My school does a choice day where students input their top choices (previously we've just said to do top 4 but we could have them pick top 5 or 6 if it helped with the coding/grouping) and then students are assigned to 4 different sessions throughout the day based on their requests. For example, one student might pick Choice 1, 3, 5, 6 and 9 and need to be put into four sessions based on those selections. They might end up in Choice 3 for their first station, choice 1 for their second, and then have to get a random option for their third and fourth stations because all their other choices filled up.
Totally understandable if that's too many levels of complex but I thought I'd throw it out there! We've had the same one teacher organize the results by hand for the whole school every year and I'd love to help simplify the process if possible. Open to suggestions!
Thanks!They might end up in Choice 3 for their first station, choice 1 for their second,
and then have to get a random option for their third and fourth stations because all their other choices filled up.
filled up?
Session A becomes unavailable like below?
I do like a challenge but I've been real busy with my real job lately. That said I think what I created already is really close to what you need. A few questions:
a) do you like the way my previous answer finds the 1st choice answer above? (I'm guessing so)
b) on the 2nd choice I see calling the above but basically saying the student can't be re-assigned the same activity.
c) now comes the big choice, after the 1st choice is made, what order should we use for the 2nd choice (and then 3rd, 4th, etc). Three options that come to mind are: a) use the same random order, b) create a new random order each time, c) use the reverse of the prior order (this reminds me of fantasy draft order, lol)
