# How would you linearize this scheduling problem? Or how would you solve this? It is variation of a set coverage problem for OpenSolver

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.

• Welcome to OR.SE. Would you please share your mathematical model in the community? It gives you more chance to answer your question by OR folks 😉. Sep 10 '20 at 9:49
• Thankyou A.Omidi, unfortunately, if I tried to put this in standard notation, I'm absolutely certain I would screw it up. It's been a very long time since I did it. Sep 10 '20 at 15:09
• Would you see this and this link? they maybe have a good tutorial on the optimization using excel. Sep 13 '20 at 4:05

We have a budget $$b_{max}$$ and the sum of costs should not exceed $$b_{max}$$ so that becomes a constraint of the model. $$X_1+X_2+X_3+...+X_N < b_{max}$$. You arrange all constraints to an equation system and solve it to get the solution vector. You can program it in Excel but it should be easier to invoke a stand-alone program if you are comfortable with a few rows of program for using the simplex algorithm or use the built-in Solver in Excel with the LP simplex algorithm.