What is the Excel formula based on the value of a cell between a specific range of numbers?
I am using Excel 2016 and I need a formula for cell W2 based on the value of cell C2, with the following logic:
if cell C2 is between 1 and 10, then it should output "R",
if cell C2 is between 11 and 20, then "B",
if cell C2 is between 21 and 30, then "Y",
if cell C2 is between 31 and 40, then, "G"
I am tinkering with the IF(AND..) formula but I am not getting it right.
This is what I have right now:
=IF(C2<=10,"R",IF(AND(C2>10,C2<=20,"B"),IF(AND(C2>20,C2<=30),"Y",IF(AND(C2>30,C2<=40),"G","!"))))
excel excel-formula excel-2016
asked Dec 29 '18 at 10:35
user3115933user3115933
1,30831631
add a comment |
I am using Excel 2016 and I need a formula for cell W2 based on the value of cell C2, with the following logic:
if cell C2 is between 1 and 10, then it should output "R",
if cell C2 is between 11 and 20, then "B",
if cell C2 is between 21 and 30, then "Y",
if cell C2 is between 31 and 40, then, "G"
I am tinkering with the IF(AND..) formula but I am not getting it right.
This is what I have right now:
=IF(C2<=10,"R",IF(AND(C2>10,C2<=20,"B"),IF(AND(C2>20,C2<=30),"Y",IF(AND(C2>30,C2<=40),"G","!"))))
excel excel-formula excel-2016
asked Dec 29 '18 at 10:35
user3115933user3115933
1,30831631
You could also start from higher values, using the order of evaluation to your advantage, i.e. if gt 30 return G, otherwise, if gt than 20 return Y, etc... Also, you could use a vlookup, rouding numbers to decades, which would avoid using deeply-nested if-else statements.
– Kanak
Dec 29 '18 at 10:42
I've answered with almost the same formula. You forgot to close the bracket after 'C2<=20'.
– VBasic2008
Dec 29 '18 at 10:59
add a comment |
I am using Excel 2016 and I need a formula for cell W2 based on the value of cell C2, with the following logic:
if cell C2 is between 1 and 10, then it should output "R",
if cell C2 is between 11 and 20, then "B",
if cell C2 is between 21 and 30, then "Y",
if cell C2 is between 31 and 40, then, "G"
I am tinkering with the IF(AND..) formula but I am not getting it right.
This is what I have right now:
=IF(C2<=10,"R",IF(AND(C2>10,C2<=20,"B"),IF(AND(C2>20,C2<=30),"Y",IF(AND(C2>30,C2<=40),"G","!"))))
excel excel-formula excel-2016
asked Dec 29 '18 at 10:35
user3115933user3115933
1,30831631
I am using Excel 2016 and I need a formula for cell W2 based on the value of cell C2, with the following logic:
if cell C2 is between 1 and 10, then it should output "R",
if cell C2 is between 11 and 20, then "B",
if cell C2 is between 21 and 30, then "Y",
if cell C2 is between 31 and 40, then, "G"
I am tinkering with the IF(AND..) formula but I am not getting it right.
This is what I have right now:
=IF(C2<=10,"R",IF(AND(C2>10,C2<=20,"B"),IF(AND(C2>20,C2<=30),"Y",IF(AND(C2>30,C2<=40),"G","!"))))
excel excel-formula excel-2016
excel excel-formula excel-2016
asked Dec 29 '18 at 10:35
user3115933user3115933
1,30831631
asked Dec 29 '18 at 10:35
user3115933user3115933
1,30831631
asked Dec 29 '18 at 10:35
user3115933user3115933
1,30831631
asked Dec 29 '18 at 10:35
user3115933user3115933
1,30831631
asked Dec 29 '18 at 10:35
user3115933user3115933
1,30831631
1,30831631
You could also start from higher values, using the order of evaluation to your advantage, i.e. if gt 30 return G, otherwise, if gt than 20 return Y, etc... Also, you could use a vlookup, rouding numbers to decades, which would avoid using deeply-nested if-else statements.
– Kanak
Dec 29 '18 at 10:42
I've answered with almost the same formula. You forgot to close the bracket after 'C2<=20'.
– VBasic2008
Dec 29 '18 at 10:59
add a comment |
You could also start from higher values, using the order of evaluation to your advantage, i.e. if gt 30 return G, otherwise, if gt than 20 return Y, etc... Also, you could use a vlookup, rouding numbers to decades, which would avoid using deeply-nested if-else statements.
– Kanak
Dec 29 '18 at 10:42
I've answered with almost the same formula. You forgot to close the bracket after 'C2<=20'.
– VBasic2008
Dec 29 '18 at 10:59
You could also start from higher values, using the order of evaluation to your advantage, i.e. if gt 30 return G, otherwise, if gt than 20 return Y, etc... Also, you could use a vlookup, rouding numbers to decades, which would avoid using deeply-nested if-else statements.
– Kanak
Dec 29 '18 at 10:42
You could also start from higher values, using the order of evaluation to your advantage, i.e. if gt 30 return G, otherwise, if gt than 20 return Y, etc... Also, you could use a vlookup, rouding numbers to decades, which would avoid using deeply-nested if-else statements.
– Kanak
Dec 29 '18 at 10:42
I've answered with almost the same formula. You forgot to close the bracket after 'C2<=20'.
– VBasic2008
Dec 29 '18 at 10:59
I've answered with almost the same formula. You forgot to close the bracket after 'C2<=20'.
– VBasic2008
Dec 29 '18 at 10:59
add a comment |
5 Answers
5
active
oldest
votes
This should do the trick:
=IF(C2<=10,"R",IF(AND(C2>10,C2<=20),"B",IF(AND(C2>20,C2<=30),"Y",IF(AND(C2>30,C2<=40),"G","!"))))
You can always double-check functions by double-click on the function suggestions and see if you used all brackets correctly:
answered Dec 29 '18 at 10:44
WizhiWizhi
3,3941830
add a comment |
I think that you should do something like this instead (e.g. using VLOOKUP):
=VLOOKUP(ROUNDUP(C2, -1), $C$4:$D$7, 2, FALSE)
Where
which avoids the use of deeply-nested if-else statements. I mean, what are you going to do if you need to do so for 20 letters? A 20-level nested if-else statement? No.
answered Dec 29 '18 at 10:49
KanakKanak
3,06031224
add a comment |
Short:
=CHOOSE(ROUNDUP(C2/10,0),"R","B","Y","G")
answered Dec 29 '18 at 11:20
pnutspnuts
48.1k76296
Simple and short +1
– skkakkar
Dec 29 '18 at 11:45
add a comment |
Classical IF AND
=IF(C2<=10,"R",IF(AND(C2>10,C2<=20),"B",IF(AND(C2>20,C2<=30),"Y",IF(AND(C2>30,C2<=40),"G","!"))))
answered Dec 29 '18 at 10:48
VBasic2008VBasic2008
2,4632314
add a comment |
Your original formula suffers from a common problem, The IF statement 'short-circuits'; that is to say that once one of the nested IF conditions is true, no further nested calculation is made. If the first IF is passed over then the second IF does not need to check if C2 is greater than 10; in fact, it has to be >10 or the second IF would never have been reached. This logic follows through to the remaining nested IFs.
=IF(C2<=10, "R", IF(C2<=20, "B" , IF(C2<=30, "Y", IF(C2<=40, "G", "!"))))
Note that your narrative states 'if cell C2 is between 1 and 10, then it should output "R"' but your formula evaluates the simpler 'less than or equal to 10' which also covers zero and negative numbers.
You can also 'hard code' a lookup.
=LOOKUP(C2, {-1E+99,1,11,21,31,41}, {"!","R","B","Y","G","!"})
answered Dec 29 '18 at 11:11
user10829321user10829321
2263
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%2f53968745%2fwhat-is-the-excel-formula-based-on-the-value-of-a-cell-between-a-specific-range%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
5 Answers
5
active
oldest
votes
5 Answers
5
active
oldest
votes
active
oldest
votes
active
oldest
votes
This should do the trick:
=IF(C2<=10,"R",IF(AND(C2>10,C2<=20),"B",IF(AND(C2>20,C2<=30),"Y",IF(AND(C2>30,C2<=40),"G","!"))))
You can always double-check functions by double-click on the function suggestions and see if you used all brackets correctly:
answered Dec 29 '18 at 10:44
WizhiWizhi
3,3941830
add a comment |
This should do the trick:
=IF(C2<=10,"R",IF(AND(C2>10,C2<=20),"B",IF(AND(C2>20,C2<=30),"Y",IF(AND(C2>30,C2<=40),"G","!"))))
You can always double-check functions by double-click on the function suggestions and see if you used all brackets correctly:
answered Dec 29 '18 at 10:44
WizhiWizhi
3,3941830
add a comment |
This should do the trick:
=IF(C2<=10,"R",IF(AND(C2>10,C2<=20),"B",IF(AND(C2>20,C2<=30),"Y",IF(AND(C2>30,C2<=40),"G","!"))))
You can always double-check functions by double-click on the function suggestions and see if you used all brackets correctly:
answered Dec 29 '18 at 10:44
WizhiWizhi
3,3941830
This should do the trick:
=IF(C2<=10,"R",IF(AND(C2>10,C2<=20),"B",IF(AND(C2>20,C2<=30),"Y",IF(AND(C2>30,C2<=40),"G","!"))))
You can always double-check functions by double-click on the function suggestions and see if you used all brackets correctly:
answered Dec 29 '18 at 10:44
WizhiWizhi
3,3941830
answered Dec 29 '18 at 10:44
WizhiWizhi
3,3941830
answered Dec 29 '18 at 10:44
WizhiWizhi
3,3941830
answered Dec 29 '18 at 10:44
WizhiWizhi
3,3941830
3,3941830
add a comment |
add a comment |
I think that you should do something like this instead (e.g. using VLOOKUP):
=VLOOKUP(ROUNDUP(C2, -1), $C$4:$D$7, 2, FALSE)
Where
which avoids the use of deeply-nested if-else statements. I mean, what are you going to do if you need to do so for 20 letters? A 20-level nested if-else statement? No.
answered Dec 29 '18 at 10:49
KanakKanak
3,06031224
add a comment |
I think that you should do something like this instead (e.g. using VLOOKUP):
=VLOOKUP(ROUNDUP(C2, -1), $C$4:$D$7, 2, FALSE)
Where
which avoids the use of deeply-nested if-else statements. I mean, what are you going to do if you need to do so for 20 letters? A 20-level nested if-else statement? No.
answered Dec 29 '18 at 10:49
KanakKanak
3,06031224
add a comment |
I think that you should do something like this instead (e.g. using VLOOKUP):
=VLOOKUP(ROUNDUP(C2, -1), $C$4:$D$7, 2, FALSE)
Where
which avoids the use of deeply-nested if-else statements. I mean, what are you going to do if you need to do so for 20 letters? A 20-level nested if-else statement? No.
answered Dec 29 '18 at 10:49
KanakKanak
3,06031224
I think that you should do something like this instead (e.g. using VLOOKUP):
=VLOOKUP(ROUNDUP(C2, -1), $C$4:$D$7, 2, FALSE)
Where
which avoids the use of deeply-nested if-else statements. I mean, what are you going to do if you need to do so for 20 letters? A 20-level nested if-else statement? No.
answered Dec 29 '18 at 10:49
KanakKanak
3,06031224
edited Dec 29 '18 at 10:56
answered Dec 29 '18 at 10:49
KanakKanak
3,06031224
answered Dec 29 '18 at 10:49
KanakKanak
3,06031224
answered Dec 29 '18 at 10:49
KanakKanak
3,06031224
3,06031224
add a comment |
add a comment |
Short:
=CHOOSE(ROUNDUP(C2/10,0),"R","B","Y","G")
answered Dec 29 '18 at 11:20
pnutspnuts
48.1k76296
Simple and short +1
– skkakkar
Dec 29 '18 at 11:45
add a comment |
Short:
=CHOOSE(ROUNDUP(C2/10,0),"R","B","Y","G")
answered Dec 29 '18 at 11:20
pnutspnuts
48.1k76296
Simple and short +1
– skkakkar
Dec 29 '18 at 11:45
add a comment |
Short:
=CHOOSE(ROUNDUP(C2/10,0),"R","B","Y","G")
answered Dec 29 '18 at 11:20
pnutspnuts
48.1k76296
Short:
=CHOOSE(ROUNDUP(C2/10,0),"R","B","Y","G")
answered Dec 29 '18 at 11:20
pnutspnuts
48.1k76296
answered Dec 29 '18 at 11:20
pnutspnuts
48.1k76296
answered Dec 29 '18 at 11:20
pnutspnuts
48.1k76296
answered Dec 29 '18 at 11:20
pnutspnuts
48.1k76296
48.1k76296
Simple and short +1
– skkakkar
Dec 29 '18 at 11:45
add a comment |
Simple and short +1
– skkakkar
Dec 29 '18 at 11:45
Simple and short +1
– skkakkar
Dec 29 '18 at 11:45
Simple and short +1
– skkakkar
Dec 29 '18 at 11:45
add a comment |
Classical IF AND
=IF(C2<=10,"R",IF(AND(C2>10,C2<=20),"B",IF(AND(C2>20,C2<=30),"Y",IF(AND(C2>30,C2<=40),"G","!"))))
answered Dec 29 '18 at 10:48
VBasic2008VBasic2008
2,4632314
add a comment |
Classical IF AND
=IF(C2<=10,"R",IF(AND(C2>10,C2<=20),"B",IF(AND(C2>20,C2<=30),"Y",IF(AND(C2>30,C2<=40),"G","!"))))
answered Dec 29 '18 at 10:48
VBasic2008VBasic2008
2,4632314
add a comment |
Classical IF AND
=IF(C2<=10,"R",IF(AND(C2>10,C2<=20),"B",IF(AND(C2>20,C2<=30),"Y",IF(AND(C2>30,C2<=40),"G","!"))))
answered Dec 29 '18 at 10:48
VBasic2008VBasic2008
2,4632314
Classical IF AND
=IF(C2<=10,"R",IF(AND(C2>10,C2<=20),"B",IF(AND(C2>20,C2<=30),"Y",IF(AND(C2>30,C2<=40),"G","!"))))
answered Dec 29 '18 at 10:48
VBasic2008VBasic2008
2,4632314
answered Dec 29 '18 at 10:48
VBasic2008VBasic2008
2,4632314
answered Dec 29 '18 at 10:48
VBasic2008VBasic2008
2,4632314
answered Dec 29 '18 at 10:48
VBasic2008VBasic2008
2,4632314
2,4632314
add a comment |
add a comment |
Your original formula suffers from a common problem, The IF statement 'short-circuits'; that is to say that once one of the nested IF conditions is true, no further nested calculation is made. If the first IF is passed over then the second IF does not need to check if C2 is greater than 10; in fact, it has to be >10 or the second IF would never have been reached. This logic follows through to the remaining nested IFs.
=IF(C2<=10, "R", IF(C2<=20, "B" , IF(C2<=30, "Y", IF(C2<=40, "G", "!"))))
Note that your narrative states 'if cell C2 is between 1 and 10, then it should output "R"' but your formula evaluates the simpler 'less than or equal to 10' which also covers zero and negative numbers.
You can also 'hard code' a lookup.
=LOOKUP(C2, {-1E+99,1,11,21,31,41}, {"!","R","B","Y","G","!"})
answered Dec 29 '18 at 11:11
user10829321user10829321
2263
add a comment |
Your original formula suffers from a common problem, The IF statement 'short-circuits'; that is to say that once one of the nested IF conditions is true, no further nested calculation is made. If the first IF is passed over then the second IF does not need to check if C2 is greater than 10; in fact, it has to be >10 or the second IF would never have been reached. This logic follows through to the remaining nested IFs.
=IF(C2<=10, "R", IF(C2<=20, "B" , IF(C2<=30, "Y", IF(C2<=40, "G", "!"))))
Note that your narrative states 'if cell C2 is between 1 and 10, then it should output "R"' but your formula evaluates the simpler 'less than or equal to 10' which also covers zero and negative numbers.
You can also 'hard code' a lookup.
=LOOKUP(C2, {-1E+99,1,11,21,31,41}, {"!","R","B","Y","G","!"})
answered Dec 29 '18 at 11:11
user10829321user10829321
2263
add a comment |
Your original formula suffers from a common problem, The IF statement 'short-circuits'; that is to say that once one of the nested IF conditions is true, no further nested calculation is made. If the first IF is passed over then the second IF does not need to check if C2 is greater than 10; in fact, it has to be >10 or the second IF would never have been reached. This logic follows through to the remaining nested IFs.
=IF(C2<=10, "R", IF(C2<=20, "B" , IF(C2<=30, "Y", IF(C2<=40, "G", "!"))))
Note that your narrative states 'if cell C2 is between 1 and 10, then it should output "R"' but your formula evaluates the simpler 'less than or equal to 10' which also covers zero and negative numbers.
You can also 'hard code' a lookup.
=LOOKUP(C2, {-1E+99,1,11,21,31,41}, {"!","R","B","Y","G","!"})
answered Dec 29 '18 at 11:11
user10829321user10829321
2263
Your original formula suffers from a common problem, The IF statement 'short-circuits'; that is to say that once one of the nested IF conditions is true, no further nested calculation is made. If the first IF is passed over then the second IF does not need to check if C2 is greater than 10; in fact, it has to be >10 or the second IF would never have been reached. This logic follows through to the remaining nested IFs.
=IF(C2<=10, "R", IF(C2<=20, "B" , IF(C2<=30, "Y", IF(C2<=40, "G", "!"))))
Note that your narrative states 'if cell C2 is between 1 and 10, then it should output "R"' but your formula evaluates the simpler 'less than or equal to 10' which also covers zero and negative numbers.
You can also 'hard code' a lookup.
=LOOKUP(C2, {-1E+99,1,11,21,31,41}, {"!","R","B","Y","G","!"})
answered Dec 29 '18 at 11:11
user10829321user10829321
2263
edited Dec 29 '18 at 11:20
answered Dec 29 '18 at 11:11
user10829321user10829321
2263
answered Dec 29 '18 at 11:11
user10829321user10829321
2263
answered Dec 29 '18 at 11:11
user10829321user10829321
2263
2263
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%2f53968745%2fwhat-is-the-excel-formula-based-on-the-value-of-a-cell-between-a-specific-range%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
You could also start from higher values, using the order of evaluation to your advantage, i.e. if gt 30 return G, otherwise, if gt than 20 return Y, etc... Also, you could use a vlookup, rouding numbers to decades, which would avoid using deeply-nested if-else statements.
– Kanak
Dec 29 '18 at 10:42
I've answered with almost the same formula. You forgot to close the bracket after 'C2<=20'.
– VBasic2008
Dec 29 '18 at 10:59