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