Allocate people into groups with balanced characteristics using Excel
TeX Python Makefile R
Switch branches/tags
Nothing to show
Clone or download
Fetching latest commit…
Cannot retrieve the latest commit at this time.
Permalink
Failed to load latest commit information.
data_analysis
orsnz_paper
orsnz_presentation
.gitignore
Group_Allocator.xlsx
LICENSE.txt
README.md
Simplified GroupAllocator.xlsx
solve_script.py

README.md

Group-Allocator

Group-Allocator is a solution developed by Oscar Dowson and Michael Fairley for allocating students to groups in a way that balances the characteristics of the students between the groups. It was first used to group 571 students for a systems engineering project at the University of Auckland with the goal to balance GPA, gender, engineering specialization and ethnicity between the groups. It is released under the MIT Licence.

The solution is implemented in a Microsoft Excel interface so it makes it easy for administrators to download and use. It requires SolverStudio, an add-in for Excel that provides "an integrated environment for optimization using modeling languages within Excel". SolverStudio can be downloaded from http://solverstudio.org. For further details, please read the "Instructions" sheet in the "Group_Allocator.xlsx" spreadsheet.

The solution has been set up for a University of Auckland context but it can be easily adapted to any situation involving group allocation. The authors are happy to provide support in customising the solution to your specific needs.

Compatible Versions of Microsoft Excel and SolverStudio

Group-Allocator has been tested on Microsoft Excel 2010 and 2013, and SolverStudio 0.08.01, 0.6.0 and 0.5.4.

Technical Details

Group-Allocator uses a mixed integer program to minimize the difference between the maximum and minimum GPA and GPA variance over all the groups, and uses relaxed constraints to spread individuals from different groups (such as a particular gender or ethnicity) over all the groups. For the full model please consult the source code within "Group_Allocator.xlsx" by using the "Show Model" function of SolverStudio.

Files

  • Group_Allocator.xlsx - the main spreadsheet that contains everything necessary to run Group-Allocator with SolverStudio installed
  • solve_script.py - a copy of the source code contained within Group_Allocator.xlsx for easy editing in a text editor and commit history in Git
  • data_analysis/data_gen.xlsx - a spreadsheet with formulae to demonstrate how test data can be generated
  • data_analysis/box_plots.R - R script for generating box plots for each factor to check quality of result
  • data_analysis/group_histograms.R - R script for generating GPA histograms for each group
  • orsnz_paper/ - contains a paper submitted to the 2014 Joint Conference of the NZ Statistical Association and Operations Research Society of NZ regarding the development, validation and application of Group-Allocator
  • orsnz_presentation/ - contains slides for presentation at 2014 Joint Conference of the NZ Statistical Association and Operations Research Society of NZ

Simplified GroupAllocator

A simplified version of the spreadsheet is contained in the file Simplified GroupAllocator.xlsx. It doesn't contain the reporting features of the full version.