1

I am a high school teacher in need of help with a student sorting problem.

  • Every 6 weeks students receive 100 "tokens" to bid on ~30 different projects.
  • They bid via a Google Form that asks them to "spend" their tokens based on interest.
  • This results in a Google Sheet that has 30 columns (1 for each project) and 373 rows (one for each student.)

I need to automate sorting of this sheet such that I end up with a new sheet that has the students who are assigned to each project.

Goals:

  1. The maximum amount of students get placed in their top "bid" projects.
  2. There are no more than 25 students in any project.
  3. The students who are classified as "level 1" are only assigned to 1 project, but the students who are classified as "level 2" are on the rosters of 2 projects. This level is connected to the e-mail that submits with the form.
  4. Any student who bids over the max 100 tokens gets randomly assigned to an open spot in a project.
  5. Projects rosters are evenly divided into 5 "hours" that have no student conflicts. E.g. if the "Science Project" and "Math Project" rosters have no students who are the same, they can be at the same time. If they do have students who are the same, they will be at different times.
  6. In the instance of a conflict, students are assigned their next highest bid. In the instance of a conflict where there is no next highest bid, students are assigned a random project that has a spot.
    • Example 1: 26 students bid 100 tokens on a project. 1 of the 26 is placed in a random project.
    • Example 2: 24 students bid 100 tokens on a project. 5 students bid 99 tokens on that same project. 4 of the 5 are assigned to the project that they bid 1 token on, if that's enough for them to "qualify." If not, they are randomly assigned to other projects.

I can sort this manually using various Google Sheets equations, but it takes me about 3 hours. I was given a quote for building a tool for automating this, but it's quite a bit out of my price range. A friend recommended that I post here. Anyone want to take it on? Not asking for a handout. I'd also be able to share a Google Sheet with example data.

EDIT: Here is a link to the process I use with student information removed. One challenge is that as I removed the student information I filled it in with "student1" etc. BUT "student1" is not the same student throughout each of the sheets, so it becomes a bit hard to track. Here is a screencast that explains my process. Let me know as you have question or ideas!

Drew Goodin
  • 27
  • 2
  • 5
    It would help if you could also please share the sheet, along with the form (perhaps an older sheet where you've applied the required formulae to get the desired output too). On a personal aside, I'd like to be able to help out on this project (peaked my interest) :) – Sourabh Choraria Oct 08 '19 at 04:55
  • 2
    Please share a copy of the Sheets and the forms. Removing any private/personal information of course. – Andres Duarte Oct 08 '19 at 10:40
  • 1
    Well, that's an interesting project which, I assume, has no easy solution. In genaral, I would probably first put all students in their highest bid (beginning with the student who bid highest on that project), if that's full, in the next one, if it's full or has time conflicts in the next one etc. The random assignment should be done at the end. But there are probably many cases where that would not work out very well. – Tobias Brösamle Oct 08 '19 at 12:05
  • Since students are only choosing one or two actives at a time,there is another possible solution. Pre-split the projects into hours beforehand, and have projects be multiple choice answers under the hour. When the project is full, use a script to remove the project from the list of choices. (I think there is also an add on for that). As this prioritizes first come first serve, students could use tokens for the year to bid on when you email them the form to sign up. – Hink Oct 08 '19 at 14:15
  • 1
    Wow, thank you all so much for the responses! [Here is a link](https://docs.google.com/spreadsheets/d/1uDoAuq40yfnwfWkOwrnVWYEX1T1ySyNnfVcNZuLTDyI/edit#gid=581720258) to the process I use with student information removed. One challenge is that as I removed the student information I filled it in with "student1" etc. BUT "student1" is not the same student throughout each of the sheets, so it becomes a bit hard to track. [Here is a screencast](https://drive.google.com/file/d/17suTu1zBPfTvxD9IURuTNrDDgXH2Q01_/view) that explains my process. Let me know as you have question or ideas! – Drew Goodin Oct 08 '19 at 17:23
  • @Hink - Unfortunately I can't pre-split into times, because we use student demand/bids to assess which projects are "greenlit." It so interesting that you mention this though, because for a different scheduling project (students opting into what time they'd like to have lunch on a daily basis), I looked to see if I could make a "responsive" Google Form. For example, the 11:30AM lunch option on the Google Form would disappear after it's full. I couldn't find a way to do this. Could you guide me in a direction? – Drew Goodin Oct 08 '19 at 17:30
  • @TobiasBrösamle Yes! That is generally my approach. – Drew Goodin Oct 08 '19 at 17:31
  • Sounds like an optimization problem. Write your equations and use a tool like solver – tehhowch Oct 09 '19 at 01:52

0 Answers0