Sorting classes into preference groups

**dhsphysics** · 11-27-2020, 08:05 AM

Hi everyone.

Looking for a way to quickly automate and work out class groups for an elective.

At the moment we do it old school with slips of paper but it takes about 2 hours (if I'm lucky) and is a lot of back and forth to get to the final result. Benefit is we almost always get them their 1st/2nd preference.

Hoping there's a way to do it faster with excel. Only way I've found so far takes just as long as doing it the old way and wasn't as good at getting everyone's top choices (lot more 3rd choices popping up)

How it works is students have a choice of 8 elective classes and choose their top 4 preferences. They do this 4 times a year and can't repeat an elective so they can't rank all 8 options.

Classes are then grouped based on that preference into classes; some classes need to be smaller than others but every class must be smaller than 20.

What I was planning was to make it a MS Form the kids filled in with their choices, giving a data set similar to this:

Name	Elective A	Elective B	Elective C	Elective D	Elective E	Elective F	Elective G	Elective H
John	3		1		2			4
Sue		3	1			2	4

Would there be a way to take this and group kids by choice, trying to maximise the amount of 1st/2nd choices, into classes, while capping classes at different levels?

Thanks for any help you can offer!

**Ochimus** · 11-28-2020, 11:52 PM

dhsphysics

Can you clarify this, please?

Do students have to attend every Elective in the course of the four terms, which suggests they cover two each term?

And as there are only eight Electives, and the students rank four in Term One but can't repeat them, that leaves only the "other" four to rank in Term Two, after which they will have nothing left to select in Terms three or four?

Ochimus

**dhsphysics** · 11-29-2020, 09:02 AM

There's 8 options, but pupils will only complete 4 of them, 1 per term.

In August they pick from the full 8. They get assigned to 1 class.

In October they pick from the 7 left after rotation one. This includes the ones they ranked in August but didn't get.

In January they pick from the 6 they haven't done yet, and in April they pick from the last 5.

So by end of year they've done 4/8.

The system doesn't need to account for that though, we get the kids to score out whichever one(s) they've done already.

**AliGW** · 11-29-2020, 09:09 AM

Windows 10 is not your version of Office - please update your profile with the correct information.

**Ochimus** · 11-29-2020, 11:28 AM

dhsphysics,

Thank you for the clarification, but raises another issue.

If thirty students select 'Elective 4' as their first choice, and the class is capped at twenty places, getting the workbook to award the Elective to twenty of them is simple.
Problem is HOW you decide WHICH twenty to choose? Alphabetically? Row number?

You could narrow the field down by having a separate matrix that "grades" the students (Strong, Average, Weak) against each Elective.
Code or formula can then "find" those that set Elective 4 their first choice, AND have a "Strong" rating in it. If there are less than the Class capacity, remaining places go to those graded "Average", and so on,

Problem comes if you have more with a particulat Grading that the capacity? If the class is twenty, and ten who chose it are graded Strong, they get placed automatically. But if there are more Averages than ten places remaining, it's back to the question of HOW you decide which students to award the remaining places?

As for omitting previous Awards from the choices in the next Round, Excel can do that for you automatically.

Ochimus

**Ochimus** · 11-29-2020, 02:54 PM

Attached shows how to set the framework to exclude previous selections.

Term 1:
Col L offers students eight options in the four DropLists. When they select one, it is removed from the other three Lists. If they "de-select" one, the choice is restored to the Other DropLists.
Each choice is added to your "Selection" DropList in U2.
In U2 you either select one of the student options in the DropList, or "overwrite" it and put in any one manually (in case not enough students included a particular course).

Term 2:
N2 shows what you assigned them in Term 1, so they are now offered only the other seven options.
Choices poulate U3
Choices, selection or overwrite as above.

Term 3:
P2 shows what you assigned from Term 2, but they are now offered only six options, because the formula excludes both courses you selected previously.
Choices populate U4
Choices, selection or overwrite as above.

Term 4:
R2 shows what you assigned from Term 3, but they now have only five options, because the formula excludes all three courses you selected previously.
Choices populate U5

As you can see under the Selection columns, there are "caveats" in using the form that need to be borne in mind.

Hope this helps

Ochimus

Ochimus