Excel conditional calculation
I have a fixed cell value $50. A1
The next column contains a ratio - typically .2 to 2.0. B1
What I want to calculate in an adjoining cell is a value based upon the ratios. C1
rules: if A1 is less than 1.0 multiply A1 by 20% and fill c1.
if A1 is greater than 1.4 multiply by 50% and fill c1
if A1 is greater than .9 or less than 1.41 C1 + A1
........if A1 is 1.0 to 1.4, multiply A1 by 100% and fill C1
help much appreciated so I can sleep..
microsoft-excel worksheet-function
edited Jan 11 at 6:22
Dave
23.3k74362
asked Jan 6 at 19:44
Trevor BooneTrevor Boone
1
add a comment |
I have a fixed cell value $50. A1
The next column contains a ratio - typically .2 to 2.0. B1
What I want to calculate in an adjoining cell is a value based upon the ratios. C1
rules: if A1 is less than 1.0 multiply A1 by 20% and fill c1.
if A1 is greater than 1.4 multiply by 50% and fill c1
if A1 is greater than .9 or less than 1.41 C1 + A1
........if A1 is 1.0 to 1.4, multiply A1 by 100% and fill C1
help much appreciated so I can sleep..
microsoft-excel worksheet-function
edited Jan 11 at 6:22
Dave
23.3k74362
asked Jan 6 at 19:44
Trevor BooneTrevor Boone
1
What's the relevance ofB1? It doesn't figure in the calculation.
– AFH
Jan 6 at 19:55
1
A1 is fixed value. So did you actually mean B1 when you say if A1 is less than 1.0 multiply A1 by 20%? Are you actually checking B1 and then including A1 in calculation?
– patkim
Jan 6 at 21:28
whoops; sorry about that - you are right; A1 is fixed, B1 is the variable AND c1 IS THE DERIVED VALUE.
– Trevor Boone
Jan 7 at 19:21
add a comment |
I have a fixed cell value $50. A1
The next column contains a ratio - typically .2 to 2.0. B1
What I want to calculate in an adjoining cell is a value based upon the ratios. C1
rules: if A1 is less than 1.0 multiply A1 by 20% and fill c1.
if A1 is greater than 1.4 multiply by 50% and fill c1
if A1 is greater than .9 or less than 1.41 C1 + A1
........if A1 is 1.0 to 1.4, multiply A1 by 100% and fill C1
help much appreciated so I can sleep..
microsoft-excel worksheet-function
edited Jan 11 at 6:22
Dave
23.3k74362
asked Jan 6 at 19:44
Trevor BooneTrevor Boone
1
I have a fixed cell value $50. A1
The next column contains a ratio - typically .2 to 2.0. B1
What I want to calculate in an adjoining cell is a value based upon the ratios. C1
rules: if A1 is less than 1.0 multiply A1 by 20% and fill c1.
if A1 is greater than 1.4 multiply by 50% and fill c1
if A1 is greater than .9 or less than 1.41 C1 + A1
........if A1 is 1.0 to 1.4, multiply A1 by 100% and fill C1
help much appreciated so I can sleep..
microsoft-excel worksheet-function
microsoft-excel worksheet-function
edited Jan 11 at 6:22
Dave
23.3k74362
asked Jan 6 at 19:44
Trevor BooneTrevor Boone
1
edited Jan 11 at 6:22
Dave
23.3k74362
asked Jan 6 at 19:44
Trevor BooneTrevor Boone
1
edited Jan 11 at 6:22
Dave
23.3k74362
edited Jan 11 at 6:22
Dave
23.3k74362
edited Jan 11 at 6:22
Dave
23.3k74362
23.3k74362
asked Jan 6 at 19:44
Trevor BooneTrevor Boone
1
asked Jan 6 at 19:44
Trevor BooneTrevor Boone
1
asked Jan 6 at 19:44
Trevor BooneTrevor Boone
1
1
What's the relevance ofB1? It doesn't figure in the calculation.
– AFH
Jan 6 at 19:55
1
A1 is fixed value. So did you actually mean B1 when you say if A1 is less than 1.0 multiply A1 by 20%? Are you actually checking B1 and then including A1 in calculation?
– patkim
Jan 6 at 21:28
whoops; sorry about that - you are right; A1 is fixed, B1 is the variable AND c1 IS THE DERIVED VALUE.
– Trevor Boone
Jan 7 at 19:21
add a comment |
What's the relevance ofB1? It doesn't figure in the calculation.
– AFH
Jan 6 at 19:55
1
A1 is fixed value. So did you actually mean B1 when you say if A1 is less than 1.0 multiply A1 by 20%? Are you actually checking B1 and then including A1 in calculation?
– patkim
Jan 6 at 21:28
whoops; sorry about that - you are right; A1 is fixed, B1 is the variable AND c1 IS THE DERIVED VALUE.
– Trevor Boone
Jan 7 at 19:21
What's the relevance of
B1? It doesn't figure in the calculation.– AFH
Jan 6 at 19:55
What's the relevance of
B1? It doesn't figure in the calculation.– AFH
Jan 6 at 19:55
1
1
A1 is fixed value. So did you actually mean B1 when you say if A1 is less than 1.0 multiply A1 by 20%? Are you actually checking B1 and then including A1 in calculation?
– patkim
Jan 6 at 21:28
A1 is fixed value. So did you actually mean B1 when you say if A1 is less than 1.0 multiply A1 by 20%? Are you actually checking B1 and then including A1 in calculation?
– patkim
Jan 6 at 21:28
whoops; sorry about that - you are right; A1 is fixed, B1 is the variable AND c1 IS THE DERIVED VALUE.
– Trevor Boone
Jan 7 at 19:21
whoops; sorry about that - you are right; A1 is fixed, B1 is the variable AND c1 IS THE DERIVED VALUE.
– Trevor Boone
Jan 7 at 19:21
add a comment |
1 Answer
1
active
oldest
votes
fill C1 with:
=A1*if(B1<1,.2,if(B1>1.4,.5,1))
This is the calcs it does:
B1 < 1, c1=a1*.2
B1 > 1, B1>1.4 then c1=A1*.5 else c1=A1*1
answered Jan 7 at 21:13
BrianBrian
1445
BRILLIANT; THANKS SO MUCH...
– Trevor Boone
Jan 8 at 0:21
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
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%2fsuperuser.com%2fquestions%2f1391241%2fexcel-conditional-calculation%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
fill C1 with:
=A1*if(B1<1,.2,if(B1>1.4,.5,1))
This is the calcs it does:
B1 < 1, c1=a1*.2
B1 > 1, B1>1.4 then c1=A1*.5 else c1=A1*1
answered Jan 7 at 21:13
BrianBrian
1445
BRILLIANT; THANKS SO MUCH...
– Trevor Boone
Jan 8 at 0:21
add a comment |
fill C1 with:
=A1*if(B1<1,.2,if(B1>1.4,.5,1))
This is the calcs it does:
B1 < 1, c1=a1*.2
B1 > 1, B1>1.4 then c1=A1*.5 else c1=A1*1
answered Jan 7 at 21:13
BrianBrian
1445
BRILLIANT; THANKS SO MUCH...
– Trevor Boone
Jan 8 at 0:21
add a comment |
fill C1 with:
=A1*if(B1<1,.2,if(B1>1.4,.5,1))
This is the calcs it does:
B1 < 1, c1=a1*.2
B1 > 1, B1>1.4 then c1=A1*.5 else c1=A1*1
answered Jan 7 at 21:13
BrianBrian
1445
fill C1 with:
=A1*if(B1<1,.2,if(B1>1.4,.5,1))
This is the calcs it does:
B1 < 1, c1=a1*.2
B1 > 1, B1>1.4 then c1=A1*.5 else c1=A1*1
answered Jan 7 at 21:13
BrianBrian
1445
answered Jan 7 at 21:13
BrianBrian
1445
answered Jan 7 at 21:13
BrianBrian
1445
answered Jan 7 at 21:13
BrianBrian
1445
1445
BRILLIANT; THANKS SO MUCH...
– Trevor Boone
Jan 8 at 0:21
add a comment |
BRILLIANT; THANKS SO MUCH...
– Trevor Boone
Jan 8 at 0:21
BRILLIANT; THANKS SO MUCH...
– Trevor Boone
Jan 8 at 0:21
BRILLIANT; THANKS SO MUCH...
– Trevor Boone
Jan 8 at 0:21
add a comment |
Thanks for contributing an answer to Super User!
- 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%2fsuperuser.com%2fquestions%2f1391241%2fexcel-conditional-calculation%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
What's the relevance of
B1? It doesn't figure in the calculation.– AFH
Jan 6 at 19:55
1
A1 is fixed value. So did you actually mean B1 when you say if A1 is less than 1.0 multiply A1 by 20%? Are you actually checking B1 and then including A1 in calculation?
– patkim
Jan 6 at 21:28
whoops; sorry about that - you are right; A1 is fixed, B1 is the variable AND c1 IS THE DERIVED VALUE.
– Trevor Boone
Jan 7 at 19:21