Nested IF Function with Multiple Conditions - either returns False or Value#
Was trying to create a formula for a team scorecard.
Was trying to refer to different columns such that if an agent belongs to Email, and is tagged Qualified, he gets to receive following payout:
KRA Score of:
- 1 = Php0
- 2 = Php0
- 3 = Php1,000
- 4 = Php3,000
- 5 = Php5,000
However, if Agent is from Voice and is qualified, he/she receives different amount as follows:
KRA Score of:
- 1 = Php0
- 2 = Php0
- 3 = Php2,000
- 4 = Php4,000
- 5 = Php6,000
Here's my sample data and corresponding payout amount same as above:
excel-formula
edited Nov 18 '18 at 18:08
Lee Mac
4,19531541
asked Nov 18 '18 at 17:46
Enthusiast_AOEnthusiast_AO
52
add a comment |
Was trying to create a formula for a team scorecard.
Was trying to refer to different columns such that if an agent belongs to Email, and is tagged Qualified, he gets to receive following payout:
KRA Score of:
- 1 = Php0
- 2 = Php0
- 3 = Php1,000
- 4 = Php3,000
- 5 = Php5,000
However, if Agent is from Voice and is qualified, he/she receives different amount as follows:
KRA Score of:
- 1 = Php0
- 2 = Php0
- 3 = Php2,000
- 4 = Php4,000
- 5 = Php6,000
Here's my sample data and corresponding payout amount same as above:
excel-formula
edited Nov 18 '18 at 18:08
Lee Mac
4,19531541
asked Nov 18 '18 at 17:46
Enthusiast_AOEnthusiast_AO
52
add a comment |
Was trying to create a formula for a team scorecard.
Was trying to refer to different columns such that if an agent belongs to Email, and is tagged Qualified, he gets to receive following payout:
KRA Score of:
- 1 = Php0
- 2 = Php0
- 3 = Php1,000
- 4 = Php3,000
- 5 = Php5,000
However, if Agent is from Voice and is qualified, he/she receives different amount as follows:
KRA Score of:
- 1 = Php0
- 2 = Php0
- 3 = Php2,000
- 4 = Php4,000
- 5 = Php6,000
Here's my sample data and corresponding payout amount same as above:
excel-formula
edited Nov 18 '18 at 18:08
Lee Mac
4,19531541
asked Nov 18 '18 at 17:46
Enthusiast_AOEnthusiast_AO
52
Was trying to create a formula for a team scorecard.
Was trying to refer to different columns such that if an agent belongs to Email, and is tagged Qualified, he gets to receive following payout:
KRA Score of:
- 1 = Php0
- 2 = Php0
- 3 = Php1,000
- 4 = Php3,000
- 5 = Php5,000
However, if Agent is from Voice and is qualified, he/she receives different amount as follows:
KRA Score of:
- 1 = Php0
- 2 = Php0
- 3 = Php2,000
- 4 = Php4,000
- 5 = Php6,000
Here's my sample data and corresponding payout amount same as above:
excel-formula
excel-formula
edited Nov 18 '18 at 18:08
Lee Mac
4,19531541
asked Nov 18 '18 at 17:46
Enthusiast_AOEnthusiast_AO
52
edited Nov 18 '18 at 18:08
Lee Mac
4,19531541
asked Nov 18 '18 at 17:46
Enthusiast_AOEnthusiast_AO
52
edited Nov 18 '18 at 18:08
Lee Mac
4,19531541
edited Nov 18 '18 at 18:08
Lee Mac
4,19531541
edited Nov 18 '18 at 18:08
Lee Mac
4,19531541
4,19531541
asked Nov 18 '18 at 17:46
Enthusiast_AOEnthusiast_AO
52
asked Nov 18 '18 at 17:46
Enthusiast_AOEnthusiast_AO
52
asked Nov 18 '18 at 17:46
Enthusiast_AOEnthusiast_AO
52
52
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
Do no, I repeat - do not use nested IFs - they are evil. They are difficult to write and impossible to maintain. VLOOKUP is only marginally better, it's a chaotic neutral. What you should do instead is to invest some of your time into learning about array formulas, named ranges/tables and INDEX/MATCH functions. There are many on-line tutorials, just Google them.
=INDEX(Payouts[#All],
MATCH([@Score],Payouts[[#All],[Score]],0),
MATCH([@Group],Payouts[#Headers],0))
Just add IF in front of the formula:
answered Nov 18 '18 at 21:46
Michal RosaMichal Rosa
1,3191814
Hello Michal - much appreciated the response. Can we also take into consideration the condition Qualified/Disqualified as we will only payout of Qualified agents? It is possible that an agent may receive a rating of 3,4,5 but is disqualified due to a gatekeeper we set (in this case, attendance score of >=95% plus KRA Rating of >=3 determines if an agent is qualified), hence should not receive a payout if tagging in Column D is disqualified (regardless if KRA Score is 3,4 or 5.
– Enthusiast_AO
Nov 19 '18 at 1:31
Please see above
– Michal Rosa
Nov 19 '18 at 1:57
Thank you so much!!! Really appreciated the help!
– Enthusiast_AO
Nov 20 '18 at 9:20
add a comment |
I would suggest the following formula to be entered into cell E2:
=IF($D2="Qualified",VLOOKUP($C2,$A$13:$C$17,IF($B2="Email",3,2),FALSE),0)
Here, the outermost IF statement checks whether the Agent is Qualified. If the Agent is Disqualified, the formula returns 0.
If so, the VLOOKUP looks up the Score from the range of scores & payouts (assuming these appear in the range $A$13:$C$17), and returns either column 3 if the Agent belongs to Email, else column 2.
The fourth argument (FALSE) supplied to the VLOOKUP function ensures that the score must appear in the supplied range for a payout to be returned. If the score is not present, the VLOOKUP will return N/A.
You could account for this by enclosing the VLOOKUP with the IFERROR function to return a predetermined value if the score is not found.
answered Nov 18 '18 at 18:14
Lee MacLee Mac
4,19531541
Hello, this did the trick! :) I am also going to start learning INDEX and MATCH as advised by Michal Rosa!
– Enthusiast_AO
Nov 19 '18 at 1:25
@Enthusiast_AO You're welcome! Please mark my answer as the solution if it answers your question, so that the question appears as resolved for others browsing the site, thanks.
– Lee Mac
Nov 19 '18 at 11:06
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%2f53363794%2fnested-if-function-with-multiple-conditions-either-returns-false-or-value%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
Do no, I repeat - do not use nested IFs - they are evil. They are difficult to write and impossible to maintain. VLOOKUP is only marginally better, it's a chaotic neutral. What you should do instead is to invest some of your time into learning about array formulas, named ranges/tables and INDEX/MATCH functions. There are many on-line tutorials, just Google them.
=INDEX(Payouts[#All],
MATCH([@Score],Payouts[[#All],[Score]],0),
MATCH([@Group],Payouts[#Headers],0))
Just add IF in front of the formula:
answered Nov 18 '18 at 21:46
Michal RosaMichal Rosa
1,3191814
Hello Michal - much appreciated the response. Can we also take into consideration the condition Qualified/Disqualified as we will only payout of Qualified agents? It is possible that an agent may receive a rating of 3,4,5 but is disqualified due to a gatekeeper we set (in this case, attendance score of >=95% plus KRA Rating of >=3 determines if an agent is qualified), hence should not receive a payout if tagging in Column D is disqualified (regardless if KRA Score is 3,4 or 5.
– Enthusiast_AO
Nov 19 '18 at 1:31
Please see above
– Michal Rosa
Nov 19 '18 at 1:57
Thank you so much!!! Really appreciated the help!
– Enthusiast_AO
Nov 20 '18 at 9:20
add a comment |
Do no, I repeat - do not use nested IFs - they are evil. They are difficult to write and impossible to maintain. VLOOKUP is only marginally better, it's a chaotic neutral. What you should do instead is to invest some of your time into learning about array formulas, named ranges/tables and INDEX/MATCH functions. There are many on-line tutorials, just Google them.
=INDEX(Payouts[#All],
MATCH([@Score],Payouts[[#All],[Score]],0),
MATCH([@Group],Payouts[#Headers],0))
Just add IF in front of the formula:
answered Nov 18 '18 at 21:46
Michal RosaMichal Rosa
1,3191814
Hello Michal - much appreciated the response. Can we also take into consideration the condition Qualified/Disqualified as we will only payout of Qualified agents? It is possible that an agent may receive a rating of 3,4,5 but is disqualified due to a gatekeeper we set (in this case, attendance score of >=95% plus KRA Rating of >=3 determines if an agent is qualified), hence should not receive a payout if tagging in Column D is disqualified (regardless if KRA Score is 3,4 or 5.
– Enthusiast_AO
Nov 19 '18 at 1:31
Please see above
– Michal Rosa
Nov 19 '18 at 1:57
Thank you so much!!! Really appreciated the help!
– Enthusiast_AO
Nov 20 '18 at 9:20
add a comment |
Do no, I repeat - do not use nested IFs - they are evil. They are difficult to write and impossible to maintain. VLOOKUP is only marginally better, it's a chaotic neutral. What you should do instead is to invest some of your time into learning about array formulas, named ranges/tables and INDEX/MATCH functions. There are many on-line tutorials, just Google them.
=INDEX(Payouts[#All],
MATCH([@Score],Payouts[[#All],[Score]],0),
MATCH([@Group],Payouts[#Headers],0))
Just add IF in front of the formula:
answered Nov 18 '18 at 21:46
Michal RosaMichal Rosa
1,3191814
Do no, I repeat - do not use nested IFs - they are evil. They are difficult to write and impossible to maintain. VLOOKUP is only marginally better, it's a chaotic neutral. What you should do instead is to invest some of your time into learning about array formulas, named ranges/tables and INDEX/MATCH functions. There are many on-line tutorials, just Google them.
=INDEX(Payouts[#All],
MATCH([@Score],Payouts[[#All],[Score]],0),
MATCH([@Group],Payouts[#Headers],0))
Just add IF in front of the formula:
answered Nov 18 '18 at 21:46
Michal RosaMichal Rosa
1,3191814
edited Nov 19 '18 at 1:56
answered Nov 18 '18 at 21:46
Michal RosaMichal Rosa
1,3191814
answered Nov 18 '18 at 21:46
Michal RosaMichal Rosa
1,3191814
answered Nov 18 '18 at 21:46
Michal RosaMichal Rosa
1,3191814
1,3191814
Hello Michal - much appreciated the response. Can we also take into consideration the condition Qualified/Disqualified as we will only payout of Qualified agents? It is possible that an agent may receive a rating of 3,4,5 but is disqualified due to a gatekeeper we set (in this case, attendance score of >=95% plus KRA Rating of >=3 determines if an agent is qualified), hence should not receive a payout if tagging in Column D is disqualified (regardless if KRA Score is 3,4 or 5.
– Enthusiast_AO
Nov 19 '18 at 1:31
Please see above
– Michal Rosa
Nov 19 '18 at 1:57
Thank you so much!!! Really appreciated the help!
– Enthusiast_AO
Nov 20 '18 at 9:20
add a comment |
Hello Michal - much appreciated the response. Can we also take into consideration the condition Qualified/Disqualified as we will only payout of Qualified agents? It is possible that an agent may receive a rating of 3,4,5 but is disqualified due to a gatekeeper we set (in this case, attendance score of >=95% plus KRA Rating of >=3 determines if an agent is qualified), hence should not receive a payout if tagging in Column D is disqualified (regardless if KRA Score is 3,4 or 5.
– Enthusiast_AO
Nov 19 '18 at 1:31
Please see above
– Michal Rosa
Nov 19 '18 at 1:57
Thank you so much!!! Really appreciated the help!
– Enthusiast_AO
Nov 20 '18 at 9:20
Hello Michal - much appreciated the response. Can we also take into consideration the condition Qualified/Disqualified as we will only payout of Qualified agents? It is possible that an agent may receive a rating of 3,4,5 but is disqualified due to a gatekeeper we set (in this case, attendance score of >=95% plus KRA Rating of >=3 determines if an agent is qualified), hence should not receive a payout if tagging in Column D is disqualified (regardless if KRA Score is 3,4 or 5.
– Enthusiast_AO
Nov 19 '18 at 1:31
Hello Michal - much appreciated the response. Can we also take into consideration the condition Qualified/Disqualified as we will only payout of Qualified agents? It is possible that an agent may receive a rating of 3,4,5 but is disqualified due to a gatekeeper we set (in this case, attendance score of >=95% plus KRA Rating of >=3 determines if an agent is qualified), hence should not receive a payout if tagging in Column D is disqualified (regardless if KRA Score is 3,4 or 5.
– Enthusiast_AO
Nov 19 '18 at 1:31
Please see above
– Michal Rosa
Nov 19 '18 at 1:57
Please see above
– Michal Rosa
Nov 19 '18 at 1:57
Thank you so much!!! Really appreciated the help!
– Enthusiast_AO
Nov 20 '18 at 9:20
Thank you so much!!! Really appreciated the help!
– Enthusiast_AO
Nov 20 '18 at 9:20
add a comment |
I would suggest the following formula to be entered into cell E2:
=IF($D2="Qualified",VLOOKUP($C2,$A$13:$C$17,IF($B2="Email",3,2),FALSE),0)
Here, the outermost IF statement checks whether the Agent is Qualified. If the Agent is Disqualified, the formula returns 0.
If so, the VLOOKUP looks up the Score from the range of scores & payouts (assuming these appear in the range $A$13:$C$17), and returns either column 3 if the Agent belongs to Email, else column 2.
The fourth argument (FALSE) supplied to the VLOOKUP function ensures that the score must appear in the supplied range for a payout to be returned. If the score is not present, the VLOOKUP will return N/A.
You could account for this by enclosing the VLOOKUP with the IFERROR function to return a predetermined value if the score is not found.
answered Nov 18 '18 at 18:14
Lee MacLee Mac
4,19531541
Hello, this did the trick! :) I am also going to start learning INDEX and MATCH as advised by Michal Rosa!
– Enthusiast_AO
Nov 19 '18 at 1:25
@Enthusiast_AO You're welcome! Please mark my answer as the solution if it answers your question, so that the question appears as resolved for others browsing the site, thanks.
– Lee Mac
Nov 19 '18 at 11:06
add a comment |
I would suggest the following formula to be entered into cell E2:
=IF($D2="Qualified",VLOOKUP($C2,$A$13:$C$17,IF($B2="Email",3,2),FALSE),0)
Here, the outermost IF statement checks whether the Agent is Qualified. If the Agent is Disqualified, the formula returns 0.
If so, the VLOOKUP looks up the Score from the range of scores & payouts (assuming these appear in the range $A$13:$C$17), and returns either column 3 if the Agent belongs to Email, else column 2.
The fourth argument (FALSE) supplied to the VLOOKUP function ensures that the score must appear in the supplied range for a payout to be returned. If the score is not present, the VLOOKUP will return N/A.
You could account for this by enclosing the VLOOKUP with the IFERROR function to return a predetermined value if the score is not found.
answered Nov 18 '18 at 18:14
Lee MacLee Mac
4,19531541
Hello, this did the trick! :) I am also going to start learning INDEX and MATCH as advised by Michal Rosa!
– Enthusiast_AO
Nov 19 '18 at 1:25
@Enthusiast_AO You're welcome! Please mark my answer as the solution if it answers your question, so that the question appears as resolved for others browsing the site, thanks.
– Lee Mac
Nov 19 '18 at 11:06
add a comment |
I would suggest the following formula to be entered into cell E2:
=IF($D2="Qualified",VLOOKUP($C2,$A$13:$C$17,IF($B2="Email",3,2),FALSE),0)
Here, the outermost IF statement checks whether the Agent is Qualified. If the Agent is Disqualified, the formula returns 0.
If so, the VLOOKUP looks up the Score from the range of scores & payouts (assuming these appear in the range $A$13:$C$17), and returns either column 3 if the Agent belongs to Email, else column 2.
The fourth argument (FALSE) supplied to the VLOOKUP function ensures that the score must appear in the supplied range for a payout to be returned. If the score is not present, the VLOOKUP will return N/A.
You could account for this by enclosing the VLOOKUP with the IFERROR function to return a predetermined value if the score is not found.
answered Nov 18 '18 at 18:14
Lee MacLee Mac
4,19531541
I would suggest the following formula to be entered into cell E2:
=IF($D2="Qualified",VLOOKUP($C2,$A$13:$C$17,IF($B2="Email",3,2),FALSE),0)
Here, the outermost IF statement checks whether the Agent is Qualified. If the Agent is Disqualified, the formula returns 0.
If so, the VLOOKUP looks up the Score from the range of scores & payouts (assuming these appear in the range $A$13:$C$17), and returns either column 3 if the Agent belongs to Email, else column 2.
The fourth argument (FALSE) supplied to the VLOOKUP function ensures that the score must appear in the supplied range for a payout to be returned. If the score is not present, the VLOOKUP will return N/A.
You could account for this by enclosing the VLOOKUP with the IFERROR function to return a predetermined value if the score is not found.
answered Nov 18 '18 at 18:14
Lee MacLee Mac
4,19531541
edited Nov 18 '18 at 18:21
answered Nov 18 '18 at 18:14
Lee MacLee Mac
4,19531541
answered Nov 18 '18 at 18:14
Lee MacLee Mac
4,19531541
answered Nov 18 '18 at 18:14
Lee MacLee Mac
4,19531541
4,19531541
Hello, this did the trick! :) I am also going to start learning INDEX and MATCH as advised by Michal Rosa!
– Enthusiast_AO
Nov 19 '18 at 1:25
@Enthusiast_AO You're welcome! Please mark my answer as the solution if it answers your question, so that the question appears as resolved for others browsing the site, thanks.
– Lee Mac
Nov 19 '18 at 11:06
add a comment |
Hello, this did the trick! :) I am also going to start learning INDEX and MATCH as advised by Michal Rosa!
– Enthusiast_AO
Nov 19 '18 at 1:25
@Enthusiast_AO You're welcome! Please mark my answer as the solution if it answers your question, so that the question appears as resolved for others browsing the site, thanks.
– Lee Mac
Nov 19 '18 at 11:06
Hello, this did the trick! :) I am also going to start learning INDEX and MATCH as advised by Michal Rosa!
– Enthusiast_AO
Nov 19 '18 at 1:25
Hello, this did the trick! :) I am also going to start learning INDEX and MATCH as advised by Michal Rosa!
– Enthusiast_AO
Nov 19 '18 at 1:25
@Enthusiast_AO You're welcome! Please mark my answer as the solution if it answers your question, so that the question appears as resolved for others browsing the site, thanks.
– Lee Mac
Nov 19 '18 at 11:06
@Enthusiast_AO You're welcome! Please mark my answer as the solution if it answers your question, so that the question appears as resolved for others browsing the site, thanks.
– Lee Mac
Nov 19 '18 at 11:06
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%2f53363794%2fnested-if-function-with-multiple-conditions-either-returns-false-or-value%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
