Can somebody say what is wrong with my nested IF function for excel?
I have the following function:
=IF(B4<5, "Excellent", IF(OR(B4>5, B4<7), "Good", IF(B4>=7, "Satisfactory")))
I want to to give excellent if the cell value is less than 5%, given Good if it is between 5 and 7 percent and give satisfactory if the cell value is bigger than 7 %.
The cell values (B4) that the function refers to, is in percentages. does anybody see what is wrong?
microsoft-excel
asked Dec 5 at 19:25
jermieje
31
add a comment |
I have the following function:
=IF(B4<5, "Excellent", IF(OR(B4>5, B4<7), "Good", IF(B4>=7, "Satisfactory")))
I want to to give excellent if the cell value is less than 5%, given Good if it is between 5 and 7 percent and give satisfactory if the cell value is bigger than 7 %.
The cell values (B4) that the function refers to, is in percentages. does anybody see what is wrong?
microsoft-excel
asked Dec 5 at 19:25
jermieje
31
add a comment |
I have the following function:
=IF(B4<5, "Excellent", IF(OR(B4>5, B4<7), "Good", IF(B4>=7, "Satisfactory")))
I want to to give excellent if the cell value is less than 5%, given Good if it is between 5 and 7 percent and give satisfactory if the cell value is bigger than 7 %.
The cell values (B4) that the function refers to, is in percentages. does anybody see what is wrong?
microsoft-excel
asked Dec 5 at 19:25
jermieje
31
I have the following function:
=IF(B4<5, "Excellent", IF(OR(B4>5, B4<7), "Good", IF(B4>=7, "Satisfactory")))
I want to to give excellent if the cell value is less than 5%, given Good if it is between 5 and 7 percent and give satisfactory if the cell value is bigger than 7 %.
The cell values (B4) that the function refers to, is in percentages. does anybody see what is wrong?
microsoft-excel
microsoft-excel
asked Dec 5 at 19:25
jermieje
31
asked Dec 5 at 19:25
jermieje
31
asked Dec 5 at 19:25
jermieje
31
asked Dec 5 at 19:25
jermieje
31
asked Dec 5 at 19:25
jermieje
31
31
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
Displaying as % is formatting; the value is stored as the actual (decimal) value, not the whole number of percent. To test for <5%, use <.05, etc.
BTW, if you want to test for a value between 5% and 7%, use AND instead of OR. OR will be true for, say, 3% because that is <7%, or 8% because that's more than 5%.
Also, you will have holes in your ranges if you test for only < and >; you need to provide for when the values are exactly equal. Figure out whether you want <= vs. >, or < vs. >=.
You can also save an IF. Your first two IFs test for everything <.07, so the false condition will be >=.07. The general form for IF is IF(condition, true result, false result). So your formula would look like this:
=IF(B4<.05, "Excellent", IF(AND(B4>=.05, B4<=.07), "Good", "Satisfactory"))
answered Dec 5 at 19:32
fixer1234
17.8k144581
add a comment |
As percentage form are between 0 and 1,You should rewrite if function like B4<0.05 ...
It should be like this:
=IF(B4<0.05, "Excellent", IF(OR(B4>0.05, B4<0.07), "Good", IF(B4>=0.07, "Satisfactory")))
As I see your IF function, you forgot to define 5% in your function
answered Dec 5 at 19:44
Nihan J. Portman
1
Welcome to Super User. Two thoughts on your answer. One is that it misses some obvious errors in the OP's formula. The other is that the intention is for each answer to provide a solution that hasn't already been contributed. This one duplicates what is already in the other answer. But do continue to participate; and thanks for taking the time to try to help on this question.
– fixer1234
Dec 5 at 19:55
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%2f1381111%2fcan-somebody-say-what-is-wrong-with-my-nested-if-function-for-excel%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
Displaying as % is formatting; the value is stored as the actual (decimal) value, not the whole number of percent. To test for <5%, use <.05, etc.
BTW, if you want to test for a value between 5% and 7%, use AND instead of OR. OR will be true for, say, 3% because that is <7%, or 8% because that's more than 5%.
Also, you will have holes in your ranges if you test for only < and >; you need to provide for when the values are exactly equal. Figure out whether you want <= vs. >, or < vs. >=.
You can also save an IF. Your first two IFs test for everything <.07, so the false condition will be >=.07. The general form for IF is IF(condition, true result, false result). So your formula would look like this:
=IF(B4<.05, "Excellent", IF(AND(B4>=.05, B4<=.07), "Good", "Satisfactory"))
answered Dec 5 at 19:32
fixer1234
17.8k144581
add a comment |
Displaying as % is formatting; the value is stored as the actual (decimal) value, not the whole number of percent. To test for <5%, use <.05, etc.
BTW, if you want to test for a value between 5% and 7%, use AND instead of OR. OR will be true for, say, 3% because that is <7%, or 8% because that's more than 5%.
Also, you will have holes in your ranges if you test for only < and >; you need to provide for when the values are exactly equal. Figure out whether you want <= vs. >, or < vs. >=.
You can also save an IF. Your first two IFs test for everything <.07, so the false condition will be >=.07. The general form for IF is IF(condition, true result, false result). So your formula would look like this:
=IF(B4<.05, "Excellent", IF(AND(B4>=.05, B4<=.07), "Good", "Satisfactory"))
answered Dec 5 at 19:32
fixer1234
17.8k144581
add a comment |
Displaying as % is formatting; the value is stored as the actual (decimal) value, not the whole number of percent. To test for <5%, use <.05, etc.
BTW, if you want to test for a value between 5% and 7%, use AND instead of OR. OR will be true for, say, 3% because that is <7%, or 8% because that's more than 5%.
Also, you will have holes in your ranges if you test for only < and >; you need to provide for when the values are exactly equal. Figure out whether you want <= vs. >, or < vs. >=.
You can also save an IF. Your first two IFs test for everything <.07, so the false condition will be >=.07. The general form for IF is IF(condition, true result, false result). So your formula would look like this:
=IF(B4<.05, "Excellent", IF(AND(B4>=.05, B4<=.07), "Good", "Satisfactory"))
answered Dec 5 at 19:32
fixer1234
17.8k144581
Displaying as % is formatting; the value is stored as the actual (decimal) value, not the whole number of percent. To test for <5%, use <.05, etc.
BTW, if you want to test for a value between 5% and 7%, use AND instead of OR. OR will be true for, say, 3% because that is <7%, or 8% because that's more than 5%.
Also, you will have holes in your ranges if you test for only < and >; you need to provide for when the values are exactly equal. Figure out whether you want <= vs. >, or < vs. >=.
You can also save an IF. Your first two IFs test for everything <.07, so the false condition will be >=.07. The general form for IF is IF(condition, true result, false result). So your formula would look like this:
=IF(B4<.05, "Excellent", IF(AND(B4>=.05, B4<=.07), "Good", "Satisfactory"))
answered Dec 5 at 19:32
fixer1234
17.8k144581
edited Dec 5 at 19:58
answered Dec 5 at 19:32
fixer1234
17.8k144581
answered Dec 5 at 19:32
fixer1234
17.8k144581
answered Dec 5 at 19:32
fixer1234
17.8k144581
17.8k144581
add a comment |
add a comment |
As percentage form are between 0 and 1,You should rewrite if function like B4<0.05 ...
It should be like this:
=IF(B4<0.05, "Excellent", IF(OR(B4>0.05, B4<0.07), "Good", IF(B4>=0.07, "Satisfactory")))
As I see your IF function, you forgot to define 5% in your function
answered Dec 5 at 19:44
Nihan J. Portman
1
Welcome to Super User. Two thoughts on your answer. One is that it misses some obvious errors in the OP's formula. The other is that the intention is for each answer to provide a solution that hasn't already been contributed. This one duplicates what is already in the other answer. But do continue to participate; and thanks for taking the time to try to help on this question.
– fixer1234
Dec 5 at 19:55
add a comment |
As percentage form are between 0 and 1,You should rewrite if function like B4<0.05 ...
It should be like this:
=IF(B4<0.05, "Excellent", IF(OR(B4>0.05, B4<0.07), "Good", IF(B4>=0.07, "Satisfactory")))
As I see your IF function, you forgot to define 5% in your function
answered Dec 5 at 19:44
Nihan J. Portman
1
Welcome to Super User. Two thoughts on your answer. One is that it misses some obvious errors in the OP's formula. The other is that the intention is for each answer to provide a solution that hasn't already been contributed. This one duplicates what is already in the other answer. But do continue to participate; and thanks for taking the time to try to help on this question.
– fixer1234
Dec 5 at 19:55
add a comment |
As percentage form are between 0 and 1,You should rewrite if function like B4<0.05 ...
It should be like this:
=IF(B4<0.05, "Excellent", IF(OR(B4>0.05, B4<0.07), "Good", IF(B4>=0.07, "Satisfactory")))
As I see your IF function, you forgot to define 5% in your function
answered Dec 5 at 19:44
Nihan J. Portman
1
As percentage form are between 0 and 1,You should rewrite if function like B4<0.05 ...
It should be like this:
=IF(B4<0.05, "Excellent", IF(OR(B4>0.05, B4<0.07), "Good", IF(B4>=0.07, "Satisfactory")))
As I see your IF function, you forgot to define 5% in your function
answered Dec 5 at 19:44
Nihan J. Portman
1
answered Dec 5 at 19:44
Nihan J. Portman
1
answered Dec 5 at 19:44
Nihan J. Portman
1
answered Dec 5 at 19:44
Nihan J. Portman
1
1
Welcome to Super User. Two thoughts on your answer. One is that it misses some obvious errors in the OP's formula. The other is that the intention is for each answer to provide a solution that hasn't already been contributed. This one duplicates what is already in the other answer. But do continue to participate; and thanks for taking the time to try to help on this question.
– fixer1234
Dec 5 at 19:55
add a comment |
Welcome to Super User. Two thoughts on your answer. One is that it misses some obvious errors in the OP's formula. The other is that the intention is for each answer to provide a solution that hasn't already been contributed. This one duplicates what is already in the other answer. But do continue to participate; and thanks for taking the time to try to help on this question.
– fixer1234
Dec 5 at 19:55
Welcome to Super User. Two thoughts on your answer. One is that it misses some obvious errors in the OP's formula. The other is that the intention is for each answer to provide a solution that hasn't already been contributed. This one duplicates what is already in the other answer. But do continue to participate; and thanks for taking the time to try to help on this question.
– fixer1234
Dec 5 at 19:55
Welcome to Super User. Two thoughts on your answer. One is that it misses some obvious errors in the OP's formula. The other is that the intention is for each answer to provide a solution that hasn't already been contributed. This one duplicates what is already in the other answer. But do continue to participate; and thanks for taking the time to try to help on this question.
– fixer1234
Dec 5 at 19:55
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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%2f1381111%2fcan-somebody-say-what-is-wrong-with-my-nested-if-function-for-excel%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
