Google Sheets – Course Allocation with Array Formula Using Constraints

google sheets

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.

Best Answer

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 |   |   |   |
+---+---+---+---+---+---+
Related Topic