Mfcttrf

A food item only made possible by time-freezing storage?

Is the mass of paint relevant in rocket design?

What exactly did this mechanic sabotage on the American Airlines 737, and how dangerous was it?

Hilbert's hotel: why can't I repeat it infinitely many times?

Cut a cake into 3 equal portions with only a knife

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

Subverting the emotional woman and stoic man trope

Fuel sender works when outside of tank, but not when in tank

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

How can an attacker use robots.txt?

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

Under what circumstances would RAM locations 0 and 1 be written and/or read on the C64?

Is the use of language other than English 'Reasonable Suspicion' for detention?

Examples of "unsuccessful" theories with afterlives

Why does this image of Jupiter look so strange?

How to create fractional SI units (SI...sqrts)?

Proper way to shut down consumer

Pi Zero Work With Embedded WIFI And Cellular USB Modem

Designing a time thief proof safe

My Project Manager does not accept carry-over in Scrum, Is that normal?

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

How can this Stack Exchange site have an animated favicon?

Hangman Game (YAHG)

Worms crawling under skin

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