7
$\begingroup$

I'm trying to create an assignment optimization model where the areas are assigned to either the south or north school districts so that the total distance is minimized. Each school must have at least 1500 students, an average income of at least $85,000 and a minority % of at least 10%.

The issue I am having is that when I use solver to find a solution by changing cells G4:G13 (H4:H13 is calculated to be the opposite), there seems to be at least one iteration where the denominator of the average income of a school is 0 (in other words, no districts assigned to one school) and of course this causes a dividing-by-0 error. I tried adding a constraint to ensure each school had at least one district in it which did nothing to solve my problem and I also tried suppressing the error with =IFERROR() which only made the model non-linear.

I need to use the Simplex LP method in solver for this assignment. Is there a way I can add these "Average" constraints without issue?

enter image description here

Improve this question
$\endgroup$

1 Answer 1

Reset to default
11
$\begingroup$

Instead of $$\frac{\textrm{Total school income}}{\textrm{Number of areas}} \ge \$ 85000$$ you could have a constraint $$\textrm{Total school income} \ge \$ 85000 \times \textrm{Number of areas}.$$

In this case, you won't have any problems when the number of areas is zero. It also makes the model linear instead of non-linear, which is usually a good thing.

An alternative would be to add constraints to require that each district has at least one area.

I would probably use both suggestions at the same time.

Improve this answer
$\endgroup$
2
  • 2
    $\begingroup$ Thank you, this is less intuitive when it is on a spreadsheet but that is alright because this solved the issue of dividing by zero. I still have another issue of not being able to meet all constraints but it seems like that issue warrants it's own post $\endgroup$
    – Jacob Myer
    Sep 21, 2019 at 19:32
  • 1
    $\begingroup$ Sounds like a good idea to make a separate post. Glad I could be of help! $\endgroup$
    – Kevin Dalmeijer
    Sep 21, 2019 at 19:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.