So, it's been about 15 years since I took my OR class in college. I'm not versed in any programming language besides a little bit of VBA.
A client of mine is looking to solve the following problem. I tried to model it in excel and use OpenSolver, but there must be something I'm missing in the configuration of the solver engine, or in the problem setup.
The idea is this: Yo have a workforce of 50 teachers, and you have 30 classrooms. For each classroom you have the demand (in number of teachers) that must be in that room by 15 minute timeslots. You have to minimize the cost of allocating teachers to rooms, while trying to respect the teachers restrictions (for example, if the teacher can't be in earlier than 8 am, or need to leave by 4 pm, or can't work more than 4 hours, or if they only want to be with the students in classrooms 1 or 2, etc.)
I tried formulating this problem as a set covering problem and tried to solve it with OpenSolver, but despite my best efforts to linearize or to reconfigure the problem, I'm kind of lost. I've uploaded a copy of my file to S3 https://examplesforsolver.s3.amazonaws.com/HowWouldYouLinearizeThis.xlsx
Also, I decided to make a quick proof of concept using excel's evolutionary solver and OpenSolver's Nomad engine, but still, didn't work. I've uploaded this file as well. https://examplesforsolver.s3.amazonaws.com/Very+Quick+Nomad+Test.xlsx
Please, take a look at the files. Any help will be greatly appreciated. But please bear in mind that neither myself or my client is versed in anything that is not excel, so coming up with a solution using Python, C++ or whatever else will not work.
Thanks a lot for your help.