Excel solver with if statements
I am trying an optimization problem with solver. One of the conditions has to to be :
if A=1 then B and or C = 1
I am trying to avoid the specific combination where all of the following are true:
A=1
B=0C=0
I tried A*10+B+C /= 10, but solver does not have an unequal constraint either.
Can someone point me in the right direction?
excel solver
asked Nov 22 '18 at 4:49
hmmmbobhmmmbob
4381021
add a comment |
I am trying an optimization problem with solver. One of the conditions has to to be :
if A=1 then B and or C = 1
I am trying to avoid the specific combination where all of the following are true:
A=1
B=0C=0
I tried A*10+B+C /= 10, but solver does not have an unequal constraint either.
Can someone point me in the right direction?
excel solver
asked Nov 22 '18 at 4:49
hmmmbobhmmmbob
4381021
Just to confirm, are you trying to write an Excel formula?
– Unsolved Cypher
Nov 22 '18 at 4:51
I know I can use IF and or in Excel, but it seems to not work with the excel build in solver, so I have to find a way around that limitation
– hmmmbob
Nov 22 '18 at 4:58
So you are trying to make an equation that will be 1 or 0 but without using IF? Can you use AND or OR or just arithmetic?
– Unsolved Cypher
Nov 22 '18 at 5:00
Also, are you saying that if A = 1, then either B, C, or both B and C are equal to -1?
– Unsolved Cypher
Nov 22 '18 at 5:11
Thanks for the edit, that clarified the conditions. But could you add whether you're able to use AND or OR or any other operators?
– Unsolved Cypher
Nov 22 '18 at 5:37
add a comment |
I am trying an optimization problem with solver. One of the conditions has to to be :
if A=1 then B and or C = 1
I am trying to avoid the specific combination where all of the following are true:
A=1
B=0C=0
I tried A*10+B+C /= 10, but solver does not have an unequal constraint either.
Can someone point me in the right direction?
excel solver
asked Nov 22 '18 at 4:49
hmmmbobhmmmbob
4381021
I am trying an optimization problem with solver. One of the conditions has to to be :
if A=1 then B and or C = 1
I am trying to avoid the specific combination where all of the following are true:
A=1
B=0C=0
I tried A*10+B+C /= 10, but solver does not have an unequal constraint either.
Can someone point me in the right direction?
excel solver
excel solver
asked Nov 22 '18 at 4:49
hmmmbobhmmmbob
4381021
asked Nov 22 '18 at 4:49
hmmmbobhmmmbob
4381021
edited Nov 22 '18 at 5:35
hmmmbob
asked Nov 22 '18 at 4:49
hmmmbobhmmmbob
4381021
asked Nov 22 '18 at 4:49
hmmmbobhmmmbob
4381021
asked Nov 22 '18 at 4:49
hmmmbobhmmmbob
4381021
4381021
Just to confirm, are you trying to write an Excel formula?
– Unsolved Cypher
Nov 22 '18 at 4:51
I know I can use IF and or in Excel, but it seems to not work with the excel build in solver, so I have to find a way around that limitation
– hmmmbob
Nov 22 '18 at 4:58
So you are trying to make an equation that will be 1 or 0 but without using IF? Can you use AND or OR or just arithmetic?
– Unsolved Cypher
Nov 22 '18 at 5:00
Also, are you saying that if A = 1, then either B, C, or both B and C are equal to -1?
– Unsolved Cypher
Nov 22 '18 at 5:11
Thanks for the edit, that clarified the conditions. But could you add whether you're able to use AND or OR or any other operators?
– Unsolved Cypher
Nov 22 '18 at 5:37
add a comment |
Just to confirm, are you trying to write an Excel formula?
– Unsolved Cypher
Nov 22 '18 at 4:51
I know I can use IF and or in Excel, but it seems to not work with the excel build in solver, so I have to find a way around that limitation
– hmmmbob
Nov 22 '18 at 4:58
So you are trying to make an equation that will be 1 or 0 but without using IF? Can you use AND or OR or just arithmetic?
– Unsolved Cypher
Nov 22 '18 at 5:00
Also, are you saying that if A = 1, then either B, C, or both B and C are equal to -1?
– Unsolved Cypher
Nov 22 '18 at 5:11
Thanks for the edit, that clarified the conditions. But could you add whether you're able to use AND or OR or any other operators?
– Unsolved Cypher
Nov 22 '18 at 5:37
Just to confirm, are you trying to write an Excel formula?
– Unsolved Cypher
Nov 22 '18 at 4:51
Just to confirm, are you trying to write an Excel formula?
– Unsolved Cypher
Nov 22 '18 at 4:51
I know I can use IF and or in Excel, but it seems to not work with the excel build in solver, so I have to find a way around that limitation
– hmmmbob
Nov 22 '18 at 4:58
I know I can use IF and or in Excel, but it seems to not work with the excel build in solver, so I have to find a way around that limitation
– hmmmbob
Nov 22 '18 at 4:58
So you are trying to make an equation that will be 1 or 0 but without using IF? Can you use AND or OR or just arithmetic?
– Unsolved Cypher
Nov 22 '18 at 5:00
So you are trying to make an equation that will be 1 or 0 but without using IF? Can you use AND or OR or just arithmetic?
– Unsolved Cypher
Nov 22 '18 at 5:00
Also, are you saying that if A = 1, then either B, C, or both B and C are equal to -1?
– Unsolved Cypher
Nov 22 '18 at 5:11
Also, are you saying that if A = 1, then either B, C, or both B and C are equal to -1?
– Unsolved Cypher
Nov 22 '18 at 5:11
Thanks for the edit, that clarified the conditions. But could you add whether you're able to use AND or OR or any other operators?
– Unsolved Cypher
Nov 22 '18 at 5:37
Thanks for the edit, that clarified the conditions. But could you add whether you're able to use AND or OR or any other operators?
– Unsolved Cypher
Nov 22 '18 at 5:37
add a comment |
2 Answers
2
active
oldest
votes
Have cell D that has the calculation that is B+C, set a constraint in the solver that that cell must be equal to 1. Or greater than or equal to 1 - not sure as you don’t clarify all states of b & c...
Then add a constraint that A must be less or equal to D.
answered Nov 22 '18 at 6:30
Solar MikeSolar Mike
2,1642515
add a comment |
If you imagine a truth table to your function you are going to get something like this:
A B C f
0 0 0 0
...
1 0 0 1
...
1 1 1 0
So we can use boolean logic operators and crate the following equivalent representation:
True on f = A and Not(B) and Not(C)
This formula return true only if A=1, B=0, C=0 and false in any other way.
Translate it to binary representation:
True is 1, false is 0, and is *, or is +, not L is (1-L)
And you get:
f = A*(1-B)*(1-C)
If you run into some more complicated conditions, you can make a complex expression. Draw your truth table, constract a similar 'ands' expression for each row where f = 1, and put an 'or' between them. Like this:
(A and Not(B) and C) or (not(A), not(B), C) or ... .
There are more efficient ways of doing this, efficient in sense of producing shorter boolean expressions. If it is important to you then you should have a look on Karnaugh maps technique.
https://en.m.wikipedia.org/wiki/Karnaugh_map
(I think the wiki article is a bit too complex for this simple operation. Maybe a YouTube example will demonstrate this better)
Otherwise it should really cover everything you need :)
answered Nov 22 '18 at 7:07
MAya MAya
464
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
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: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
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/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
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%2fstackoverflow.com%2fquestions%2f53424066%2fexcel-solver-with-if-statements%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
Have cell D that has the calculation that is B+C, set a constraint in the solver that that cell must be equal to 1. Or greater than or equal to 1 - not sure as you don’t clarify all states of b & c...
Then add a constraint that A must be less or equal to D.
answered Nov 22 '18 at 6:30
Solar MikeSolar Mike
2,1642515
add a comment |
Have cell D that has the calculation that is B+C, set a constraint in the solver that that cell must be equal to 1. Or greater than or equal to 1 - not sure as you don’t clarify all states of b & c...
Then add a constraint that A must be less or equal to D.
answered Nov 22 '18 at 6:30
Solar MikeSolar Mike
2,1642515
add a comment |
Have cell D that has the calculation that is B+C, set a constraint in the solver that that cell must be equal to 1. Or greater than or equal to 1 - not sure as you don’t clarify all states of b & c...
Then add a constraint that A must be less or equal to D.
answered Nov 22 '18 at 6:30
Solar MikeSolar Mike
2,1642515
Have cell D that has the calculation that is B+C, set a constraint in the solver that that cell must be equal to 1. Or greater than or equal to 1 - not sure as you don’t clarify all states of b & c...
Then add a constraint that A must be less or equal to D.
answered Nov 22 '18 at 6:30
Solar MikeSolar Mike
2,1642515
edited Nov 22 '18 at 6:51
answered Nov 22 '18 at 6:30
Solar MikeSolar Mike
2,1642515
answered Nov 22 '18 at 6:30
Solar MikeSolar Mike
2,1642515
answered Nov 22 '18 at 6:30
Solar MikeSolar Mike
2,1642515
2,1642515
add a comment |
add a comment |
If you imagine a truth table to your function you are going to get something like this:
A B C f
0 0 0 0
...
1 0 0 1
...
1 1 1 0
So we can use boolean logic operators and crate the following equivalent representation:
True on f = A and Not(B) and Not(C)
This formula return true only if A=1, B=0, C=0 and false in any other way.
Translate it to binary representation:
True is 1, false is 0, and is *, or is +, not L is (1-L)
And you get:
f = A*(1-B)*(1-C)
If you run into some more complicated conditions, you can make a complex expression. Draw your truth table, constract a similar 'ands' expression for each row where f = 1, and put an 'or' between them. Like this:
(A and Not(B) and C) or (not(A), not(B), C) or ... .
There are more efficient ways of doing this, efficient in sense of producing shorter boolean expressions. If it is important to you then you should have a look on Karnaugh maps technique.
https://en.m.wikipedia.org/wiki/Karnaugh_map
(I think the wiki article is a bit too complex for this simple operation. Maybe a YouTube example will demonstrate this better)
Otherwise it should really cover everything you need :)
answered Nov 22 '18 at 7:07
MAya MAya
464
add a comment |
If you imagine a truth table to your function you are going to get something like this:
A B C f
0 0 0 0
...
1 0 0 1
...
1 1 1 0
So we can use boolean logic operators and crate the following equivalent representation:
True on f = A and Not(B) and Not(C)
This formula return true only if A=1, B=0, C=0 and false in any other way.
Translate it to binary representation:
True is 1, false is 0, and is *, or is +, not L is (1-L)
And you get:
f = A*(1-B)*(1-C)
If you run into some more complicated conditions, you can make a complex expression. Draw your truth table, constract a similar 'ands' expression for each row where f = 1, and put an 'or' between them. Like this:
(A and Not(B) and C) or (not(A), not(B), C) or ... .
There are more efficient ways of doing this, efficient in sense of producing shorter boolean expressions. If it is important to you then you should have a look on Karnaugh maps technique.
https://en.m.wikipedia.org/wiki/Karnaugh_map
(I think the wiki article is a bit too complex for this simple operation. Maybe a YouTube example will demonstrate this better)
Otherwise it should really cover everything you need :)
answered Nov 22 '18 at 7:07
MAya MAya
464
add a comment |
If you imagine a truth table to your function you are going to get something like this:
A B C f
0 0 0 0
...
1 0 0 1
...
1 1 1 0
So we can use boolean logic operators and crate the following equivalent representation:
True on f = A and Not(B) and Not(C)
This formula return true only if A=1, B=0, C=0 and false in any other way.
Translate it to binary representation:
True is 1, false is 0, and is *, or is +, not L is (1-L)
And you get:
f = A*(1-B)*(1-C)
If you run into some more complicated conditions, you can make a complex expression. Draw your truth table, constract a similar 'ands' expression for each row where f = 1, and put an 'or' between them. Like this:
(A and Not(B) and C) or (not(A), not(B), C) or ... .
There are more efficient ways of doing this, efficient in sense of producing shorter boolean expressions. If it is important to you then you should have a look on Karnaugh maps technique.
https://en.m.wikipedia.org/wiki/Karnaugh_map
(I think the wiki article is a bit too complex for this simple operation. Maybe a YouTube example will demonstrate this better)
Otherwise it should really cover everything you need :)
answered Nov 22 '18 at 7:07
MAya MAya
464
If you imagine a truth table to your function you are going to get something like this:
A B C f
0 0 0 0
...
1 0 0 1
...
1 1 1 0
So we can use boolean logic operators and crate the following equivalent representation:
True on f = A and Not(B) and Not(C)
This formula return true only if A=1, B=0, C=0 and false in any other way.
Translate it to binary representation:
True is 1, false is 0, and is *, or is +, not L is (1-L)
And you get:
f = A*(1-B)*(1-C)
If you run into some more complicated conditions, you can make a complex expression. Draw your truth table, constract a similar 'ands' expression for each row where f = 1, and put an 'or' between them. Like this:
(A and Not(B) and C) or (not(A), not(B), C) or ... .
There are more efficient ways of doing this, efficient in sense of producing shorter boolean expressions. If it is important to you then you should have a look on Karnaugh maps technique.
https://en.m.wikipedia.org/wiki/Karnaugh_map
(I think the wiki article is a bit too complex for this simple operation. Maybe a YouTube example will demonstrate this better)
Otherwise it should really cover everything you need :)
answered Nov 22 '18 at 7:07
MAya MAya
464
edited Nov 22 '18 at 7:14
answered Nov 22 '18 at 7:07
MAya MAya
464
answered Nov 22 '18 at 7:07
MAya MAya
464
answered Nov 22 '18 at 7:07
MAya MAya
464
464
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- 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.
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%2fstackoverflow.com%2fquestions%2f53424066%2fexcel-solver-with-if-statements%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
Just to confirm, are you trying to write an Excel formula?
– Unsolved Cypher
Nov 22 '18 at 4:51
I know I can use IF and or in Excel, but it seems to not work with the excel build in solver, so I have to find a way around that limitation
– hmmmbob
Nov 22 '18 at 4:58
So you are trying to make an equation that will be 1 or 0 but without using IF? Can you use AND or OR or just arithmetic?
– Unsolved Cypher
Nov 22 '18 at 5:00
Also, are you saying that if A = 1, then either B, C, or both B and C are equal to -1?
– Unsolved Cypher
Nov 22 '18 at 5:11
Thanks for the edit, that clarified the conditions. But could you add whether you're able to use AND or OR or any other operators?
– Unsolved Cypher
Nov 22 '18 at 5:37