Back in 2014, a group of male students had the creative but misguided idea of holding a prom draft to select dates for prom, a formal dance typically held at the end of high school. The very obvious problems involved having payments for high draft picks and taking the girls’ preferences out of the process.
With that in mind, we thought instead of a Prom draft, why not organize a Prom match? The matching system for medical residency has been in place since 1952, and even led to a Nobel Prize. In the residency match system, each applicant ranks his or her acceptable choices, and each school ranks its acceptable residents. Then the algorithm attempts to make the best or most optimal matches, among all parties.
So, let’s dive into how we can create a spreadsheet that optimizes students’ date preferences for prom.
Spreadsheet Inputs
Our example includes a group of 5 boys and 5 girls, who each list the preferred rank order of the girls or boys they’d like to go to prom with. For example, Aaron’s top choice is Brittany, second choice is Chloe, and third choice is Elizabeth. Brittany is only willing to go with Charlie and David.
Spreadsheet Outputs and Logic
While our approach to the matching problem isn’t exactly how the matching algorithm works, it results in the best matches by optimizing preferences across all parties. For each combination of people, we multiply the ranking for female’s preference with the male’s preference. For example, if Aaron ranks Chloe #2 and Chloe ranks Aaron #3, their combined match score would be 6 (2 x 3). The lowest match scores will be the best matches.
1. Making tables out of the ranking information
First, we’ll make a 5×5 table with all the boys and all the girls. Inside the table, we reflect the male preferences by using the match function to find what rank each male ranked the female. If the male didn’t rank the female (which we figure out with the ISNA() function), we’ll return a 0.
We do the same thing for the females preferences:
2. Multiply the two tables together to figure out the strength of each match.
First, we have to transpose the female table, so that the cells for the female preference of the males lines up with the male preference of the females. This requires the TRANSPOSE() function and the (extremely unintuitive) array formula trick (CTRL+SHIFT+ENTER)
Now, we just multiply the two preference tables together:
3. Make a list of all the combinations, find the score, then rank them and take out the zeros, then sort by the match strength.
First we list all 25 combinations of females and males, then look up the match strength in the final table using our index() and match() functions:
Then highlight the table, and go to data tab, then filter. Then filter out the zeros in the Match Score column, then sort ascending:
Finally, we add a column to check to see if someone has previously been assigned to a stronger match – for instance, in Row 51, Annie and Charlie are matched with strength 3, but both of them have previously been matched to a better match.
Now we can add a filter to the last column, to filter out the “No” values, to get our final list:
Check out our spreadsheet here: Prom and Residency Match