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…?
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…?
$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
edited 8 hours ago
TheSimpliFire♦
2,5197 silver badges39 bronze badges
asked 8 hours ago
Jacob MyerJacob Myer
233 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
edited 8 hours ago
TheSimpliFire♦
2,5197 silver badges39 bronze badges
asked 8 hours ago
Jacob MyerJacob Myer
233 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
edited 8 hours ago
TheSimpliFire♦
2,5197 silver badges39 bronze badges
asked 8 hours ago
Jacob MyerJacob Myer
233 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$
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
optimization linear-programming solver binary-variable assignment-problem
edited 8 hours ago
TheSimpliFire♦
2,5197 silver badges39 bronze badges
asked 8 hours ago
Jacob MyerJacob Myer
233 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,5197 silver badges39 bronze badges
asked 8 hours ago
Jacob MyerJacob Myer
233 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,5197 silver badges39 bronze badges
edited 8 hours ago
TheSimpliFire♦
2,5197 silver badges39 bronze badges
edited 8 hours ago
TheSimpliFire♦
2,5197 silver badges39 bronze badges
2,5197 silver badges39 bronze badges
asked 8 hours ago
Jacob MyerJacob Myer
233 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
233 bronze badges
asked 8 hours ago
Jacob MyerJacob Myer
233 bronze badges
233 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.
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.
add a comment
|
add a comment
|
1 Answer
1
active
oldest
votes
$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.
answered 7 hours ago
Kevin DalmeijerKevin Dalmeijer
3,3327 silver badges33 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.
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
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
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
$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.
answered 7 hours ago
Kevin DalmeijerKevin Dalmeijer
3,3327 silver badges33 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.
answered 7 hours ago
Kevin DalmeijerKevin Dalmeijer
3,3327 silver badges33 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.
answered 7 hours ago
Kevin DalmeijerKevin Dalmeijer
3,3327 silver badges33 bronze badges
$endgroup$
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.
answered 7 hours ago
Kevin DalmeijerKevin Dalmeijer
3,3327 silver badges33 bronze badges
answered 7 hours ago
Kevin DalmeijerKevin Dalmeijer
3,3327 silver badges33 bronze badges
answered 7 hours ago
Kevin DalmeijerKevin Dalmeijer
3,3327 silver badges33 bronze badges
answered 7 hours ago
Kevin DalmeijerKevin Dalmeijer
3,3327 silver badges33 bronze badges
3,3327 silver badges33 bronze badges
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
|
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
1
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$
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
$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
|
Jacob Myer is a new contributor. Be nice, and check out our Code of Conduct.
Jacob Myer is a new contributor. Be nice, and check out our Code of Conduct.
Jacob Myer is a new contributor. Be nice, and check out our Code of Conduct.
Jacob Myer is a new contributor. Be nice, and check out our Code of Conduct.
Thanks for contributing an answer to Operations Research Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
Use MathJax to format equations. MathJax reference.
To learn more, see our tips on writing great answers.
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
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
Required, but never shown
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
Required, but never shown
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
Required, but never shown
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
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
