Xjyuk

How 象【しょう】 ( ≈かたち、 すがた、ようす) and 象【ぞう】 (どうぶつ) got to be written with the same kanji?

How to deal with a Homophobic PC

Why did UK NHS pay for homeopathic treatments?

Tesla coil and Tesla tower

A famous scholar sent me an unpublished draft of hers. Then she died. I think her work should be published. What should I do?

Is it acceptable to say that a reviewer's concern is not going to be addressed because then the paper would be too long?

sed command works in terminal but not through bash_aliases or a bash script

Is there any relation/leak between two sections of LM358 op-amp?

Pi Zero Work With Embedded WIFI And Cellular USB Modem

Is it impolite to ask for halal food when traveling to and in Thailand?

A file manager to open a zip file like opening a folder, instead of extract it by using a archive manager

How can an attacker use robots.txt?

Why does (inf + 0j)*1 evaluate to inf + nanj?

Lost Update Understanding

Going to France with limited French for a day

What should I consider when deciding whether to delay an exam?

List of 1000 most common words across all languages

Do I have advantage with Riposte when moving away from a flanked enemy and triggering an opportunity attack?

Cut a cake into 3 equal portions with only a knife

How can this Stack Exchange site have an animated favicon?

Symbol for function composition like a big sum

What benefits does the Power Word Kill spell have?

I am 15 years old and do not go to a Yeshiva but would like to learn Talmud. A few rabbis near me said they could teach me. How should I start

Received a package but didn't order it

Excel Solver linear programming - Is it possible to use average of values as a constraint without #DIV/0! errors or sacrificing linearity?

Solver rounding precision vs programming language rounding precisionApplication of complex numbers in Linear Programming?How to select a Constraint Programming SolverPricing of blends/mixtures across multiple timestepsMixed-Integer Linear Programming (Capacity Planning)Linear and Integer programming materialsFind feasible point in polynomial time in linear programmingMachine Allocation & optimal Utilization using pythonWhen should I use a solver for IP and MIP and can I just use a library from Python, R, Matlab, etc…?

4

$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?

optimization linear-programming solver binary-variable assignment-problem

share|improve this question

edited 8 hours ago

TheSimpliFire♦

2,51977 silver badges3939 bronze badges

asked 8 hours ago

Jacob MyerJacob Myer

2333 bronze badges

New contributor

Jacob Myer is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

$endgroup$

add a comment
 | 

4

$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?

optimization linear-programming solver binary-variable assignment-problem

share|improve this question

edited 8 hours ago

TheSimpliFire♦

2,51977 silver badges3939 bronze badges

asked 8 hours ago

Jacob MyerJacob Myer

2333 bronze badges

New contributor

Jacob Myer is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

$endgroup$

add a comment
 | 

$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?

optimization linear-programming solver binary-variable assignment-problem

share|improve this question

edited 8 hours ago

TheSimpliFire♦

2,51977 silver badges3939 bronze badges

asked 8 hours ago

Jacob MyerJacob Myer

2333 bronze badges

New contributor

Jacob Myer is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

$endgroup$

share|improve this question

edited 8 hours ago

TheSimpliFire♦

2,51977 silver badges3939 bronze badges

asked 8 hours ago

Jacob MyerJacob Myer

2333 bronze badges

New contributor

Jacob Myer is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

share|improve this question

edited 8 hours ago

TheSimpliFire♦

2,51977 silver badges3939 bronze badges

asked 8 hours ago

Jacob MyerJacob Myer

2333 bronze badges

New contributor

Jacob Myer is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

edited 8 hours ago

TheSimpliFire♦

2,51977 silver badges3939 bronze badges

edited 8 hours ago

TheSimpliFire♦

2,51977 silver badges3939 bronze badges

asked 8 hours ago

Jacob MyerJacob Myer

2333 bronze badges

New contributor

Jacob Myer is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked 8 hours ago

Jacob MyerJacob Myer

2333 bronze badges

1 Answer
1

active

oldest

votes

7

$begingroup$

Instead of
$$fractextrmTotal school incometextrmNumber of areas ge $ 85000$$
you could have a constraint
$$textrmTotal school income ge $ 85000 times textrmNumber 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.

share|improve this answer

answered 7 hours ago

Kevin DalmeijerKevin Dalmeijer

3,33277 silver badges3333 bronze badges

$endgroup$

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  $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
  5 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  $begingroup$
  Sounds like a good idea to make a separate post. Glad I could be of help!
  $endgroup$
  – Kevin Dalmeijer
  5 hours ago
  

  
  
  
  

add a comment
 | 

Your Answer

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "700"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/4.0/"u003ecc by-sa 4.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
noCode: true, onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

Jacob Myer is a new contributor. Be nice, and check out our Code of Conduct.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2for.stackexchange.com%2fquestions%2f2615%2fexcel-solver-linear-programming-is-it-possible-to-use-average-of-values-as-a-c%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

7

$begingroup$

Instead of
$$fractextrmTotal school incometextrmNumber of areas ge $ 85000$$
you could have a constraint
$$textrmTotal school income ge $ 85000 times textrmNumber 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.

share|improve this answer

answered 7 hours ago

Kevin DalmeijerKevin Dalmeijer

3,33277 silver badges3333 bronze badges

$endgroup$

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  $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
  5 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  $begingroup$
  Sounds like a good idea to make a separate post. Glad I could be of help!
  $endgroup$
  – Kevin Dalmeijer
  5 hours ago
  

  
  
  
  

add a comment
 | 

7

$begingroup$

Instead of
$$fractextrmTotal school incometextrmNumber of areas ge $ 85000$$
you could have a constraint
$$textrmTotal school income ge $ 85000 times textrmNumber 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.

share|improve this answer

answered 7 hours ago

Kevin DalmeijerKevin Dalmeijer

3,33277 silver badges3333 bronze badges

$endgroup$

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  $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
  5 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  $begingroup$
  Sounds like a good idea to make a separate post. Glad I could be of help!
  $endgroup$
  – Kevin Dalmeijer
  5 hours ago
  

  
  
  
  

add a comment
 | 

$begingroup$

Instead of
$$fractextrmTotal school incometextrmNumber of areas ge $ 85000$$
you could have a constraint
$$textrmTotal school income ge $ 85000 times textrmNumber 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.

share|improve this answer

answered 7 hours ago

Kevin DalmeijerKevin Dalmeijer

3,33277 silver badges3333 bronze badges

$endgroup$

share|improve this answer

answered 7 hours ago

Kevin DalmeijerKevin Dalmeijer

3,33277 silver badges3333 bronze badges

answered 7 hours ago

Kevin DalmeijerKevin Dalmeijer

3,33277 silver badges3333 bronze badges

answered 7 hours ago

Kevin DalmeijerKevin Dalmeijer

3,33277 silver badges3333 bronze badges