3

I try to assign students to courses based on the student's preference for each course and course capacity. Students are ranked based previous performance. In the current implementation, the data is obtained via Google Forms and other data sources. The aim is to use arrayformula() everywhere so that no manual processing would be required.

A very simplified sample data sheet without any formulas or external connections can be found here: sample data

Top students are at the top and their choice is processed first. A possible desired output (calculated manually) is also shown.

I can, with array formulas easily identify the first choices and calculate the accumulated sum for each course, but where I'm struggling with is applying the constraint once a class has reached it's limit: my formulas start having circular reference errors or out of bounds errors (when using offset's).

I would appreciate some guidance from the community.

  • The sample data now have the custom function from the answer by Normal Human. The sample files should be kept unchanged or the data should be added to the body of the question so others could review the question as was before applying the answer suggestions . The OP could add a link to another file with your attempt to apply the suggestions from one or several answers as result of the refinement of the question. By the way I have a similar question but is a bit complex. – Rubén Aug 8 '15 at 14:09
  • Good point Normal human... I have restored the sample file to it's original state when the question was asked. and a new solution file an be found here: link – Generic IT Account Aug 8 '15 at 16:00
2

Trying to do this with built-in spreadsheet functions is sure to be frustrating. But the problem is easily solved with a custom function that returns an array:

=allocate(B3:G32, B36:G36)

The first argument is the list of preferences, the second is the list of class sizes. The output is a rectangular array with 1 marking enrollment. The function does not give you the actual enrollment in classes, which you can easily find with SUM.

Here is the function I wrote: it should be pasted into Script Editor, Tools > Script Editor...

function allocate(prefs, limits) {
  var remaining = limits[0], output = [];
  for (var i=0; i<prefs.length; i++) {
    output.push([]);
    for (var j=1; j<=prefs[i].length; j++) {
      var k = prefs[i].indexOf(j);
      if (k != -1 && remaining[k] > 0) {
        output[i][k] = 1; 
        remaining[k]--;
        break;
      }
    }
  }   
  return output;
}

Output for your example agrees with yours:

+---+---+---+---+---+---+
|   |   |   | 1 |   |   |
|   |   |   | 1 |   |   |
|   |   |   | 1 |   |   |
|   | 1 |   |   |   |   |
|   |   |   | 1 |   |   |
|   |   |   |   |   | 1 |
|   |   |   |   |   | 1 |
| 1 |   |   |   |   |   |
|   |   |   | 1 |   |   |
| 1 |   |   |   |   |   |
| 1 |   |   |   |   |   |
|   |   |   |   | 1 |   |
| 1 |   |   |   |   |   |
|   |   |   |   |   | 1 |
| 1 |   |   |   |   |   |
|   |   |   |   |   | 1 |
|   |   |   |   |   | 1 |
|   |   |   |   | 1 |   |
|   |   |   |   | 1 |   |
|   |   |   |   | 1 |   |
|   |   |   |   | 1 |   |
|   |   | 1 |   |   |   |
|   | 1 |   |   |   |   |
|   |   | 1 |   |   |   |
|   |   |   |   | 1 |   |
|   | 1 |   |   |   |   |
|   | 1 |   |   |   |   |
|   |   | 1 |   |   |   |
|   |   | 1 |   |   |   |
|   |   | 1 |   |   |   |
+---+---+---+---+---+---+
  • Thanks for the script solution, Normal Human. It was going to be my fallback solution if I did not find appropriate spreadsheet formulas. -- Peter – Generic IT Account Aug 8 '15 at 7:03
  • 1
    I can imagine doing what you want purely with built-in functions, but it would require using auxiliary cells outside of the output range (computing the availability of classes), then filtering preferences by availability, then picking the optimal... it's just a minefield of complex typo-prone formulas, most likely working slower than a single call to a custom formula. And since the calculation involves 100+ cells depending on each other in complex ways, the whole chain of dependencies may never even resolve. – user79865 Aug 8 '15 at 7:09
  • 1
    @GenericITAccount in the meantime, in my copy I moved the summary cells from the bottom to the top. The following formula should solve problem 1 =AllocateCourse(filter(B8:G,Len(B8:B)),B3:G3). – Rubén Aug 8 '15 at 15:55
  • 1
    @GenericITAccount If your data is very large, consider time-based script that processes it every hour (instead of real time). Same logic, except the inputs are obtained via getValues method from some range, and output is placed via setValues. The details of ranges would depend on how the data is stored. A function in Google Apps Script can be executed every hour automatically by adding a trigger via the script editor. – user79865 Aug 8 '15 at 18:31
  • 1
    @GenericITAccount Indeed, the loop over j was iterating over 0...5 instead of 1...6. You know... xkcd.com/163 fixed now. – user79865 Aug 12 '15 at 21:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.