Hello all,
I've recently started exploring solver, but I've hit a wall with using it on this particular problem. I am trying to optimize the allocation of students to courses in a school based on a number of parameters and constraints. I built out a cost function to use in simulated annealing in matlab, but I'm trying to see if I can do the same thing directly in Excel (with less code writing). I put together a simplified version just to test, but I'm not getting the results I expected from solver.
Here is the basic situation (apologies, I am not able to upload the file at this time):
We have a group of students who have each indicated an order of preference between three available classes. I have put the names of the students in column A, the first choices in column B, the second choices in column C, and the third choices in column D.
In Column E, I have assigned each student to a class based on their indicated preference.
In column F, I have assigned a cost to each student's assignment as follows: =IF(given class = first choice, 0 , if(given class = second choice , 1 , 2).
I skipped column G.
In Column H, I listed out the possible classes: 1, 2, and 3.
In Column I, I calculated the size of the classes.
In Cell J2, I calculated a simple cost of the assignment as the cum of the individual student costs.
At this point, I moved to solver to optimize my student assignment. I clicked on Solver, set my target cell to $J$2, set it equal to Min, set my changing cells to the nonblank cells of column E, then added the constraints that those changing cells had to be integers between 1 and 3 (inclusive).
When I hit 'run', I receive the error message 'Solver could not find a feasible solution.'
At first, I thought that I might simply have too many students and be overloading the solver engine. But I set up the same model with only six students and their choices designed to allow a single optimal solution, and I still receive the same error. Any help would be much appreciated!
I've recently started exploring solver, but I've hit a wall with using it on this particular problem. I am trying to optimize the allocation of students to courses in a school based on a number of parameters and constraints. I built out a cost function to use in simulated annealing in matlab, but I'm trying to see if I can do the same thing directly in Excel (with less code writing). I put together a simplified version just to test, but I'm not getting the results I expected from solver.
Here is the basic situation (apologies, I am not able to upload the file at this time):
We have a group of students who have each indicated an order of preference between three available classes. I have put the names of the students in column A, the first choices in column B, the second choices in column C, and the third choices in column D.
In Column E, I have assigned each student to a class based on their indicated preference.
In column F, I have assigned a cost to each student's assignment as follows: =IF(given class = first choice, 0 , if(given class = second choice , 1 , 2).
I skipped column G.
In Column H, I listed out the possible classes: 1, 2, and 3.
In Column I, I calculated the size of the classes.
In Cell J2, I calculated a simple cost of the assignment as the cum of the individual student costs.
At this point, I moved to solver to optimize my student assignment. I clicked on Solver, set my target cell to $J$2, set it equal to Min, set my changing cells to the nonblank cells of column E, then added the constraints that those changing cells had to be integers between 1 and 3 (inclusive).
When I hit 'run', I receive the error message 'Solver could not find a feasible solution.'
At first, I thought that I might simply have too many students and be overloading the solver engine. But I set up the same model with only six students and their choices designed to allow a single optimal solution, and I still receive the same error. Any help would be much appreciated!
