How to Shorten Formula IF with too many conditions?
I am trying to create a formula for my Aging in Accounts Receivable - Tuition Fees [See Table 2: Accounts Receivable Schedule].
Every month, a student must achieve a certain balance or else, its excess will fall over due [See Table 1: Student Tuition Fee Balance Matrix].
So there are three conditions, the month we are currently in, the school level of the student and if his balance to date is greater than the matrix [over due] or less [no over due balance].
The Formula
=IF(B8<N9,IF(B8<M9,IF(B8<L9,IF(B8<K9,IF(B8<J9,IF(B8<I9,IF(B8<H9,IF(B8<G9,IF(B8<F9,IF(B8<E9,0,"FALSE"),E10),F10),G10),H10),I10),J10),K10),L10),M10)
worksheet-function microsoft-excel-2010
edited Jan 25 at 2:31
angelofdev
929120
asked Jan 25 at 1:03
A.QueA.Que
11
add a comment |
I am trying to create a formula for my Aging in Accounts Receivable - Tuition Fees [See Table 2: Accounts Receivable Schedule].
Every month, a student must achieve a certain balance or else, its excess will fall over due [See Table 1: Student Tuition Fee Balance Matrix].
So there are three conditions, the month we are currently in, the school level of the student and if his balance to date is greater than the matrix [over due] or less [no over due balance].
The Formula
=IF(B8<N9,IF(B8<M9,IF(B8<L9,IF(B8<K9,IF(B8<J9,IF(B8<I9,IF(B8<H9,IF(B8<G9,IF(B8<F9,IF(B8<E9,0,"FALSE"),E10),F10),G10),H10),I10),J10),K10),L10),M10)
worksheet-function microsoft-excel-2010
edited Jan 25 at 2:31
angelofdev
929120
asked Jan 25 at 1:03
A.QueA.Que
11
add a comment |
I am trying to create a formula for my Aging in Accounts Receivable - Tuition Fees [See Table 2: Accounts Receivable Schedule].
Every month, a student must achieve a certain balance or else, its excess will fall over due [See Table 1: Student Tuition Fee Balance Matrix].
So there are three conditions, the month we are currently in, the school level of the student and if his balance to date is greater than the matrix [over due] or less [no over due balance].
The Formula
=IF(B8<N9,IF(B8<M9,IF(B8<L9,IF(B8<K9,IF(B8<J9,IF(B8<I9,IF(B8<H9,IF(B8<G9,IF(B8<F9,IF(B8<E9,0,"FALSE"),E10),F10),G10),H10),I10),J10),K10),L10),M10)
worksheet-function microsoft-excel-2010
edited Jan 25 at 2:31
angelofdev
929120
asked Jan 25 at 1:03
A.QueA.Que
11
I am trying to create a formula for my Aging in Accounts Receivable - Tuition Fees [See Table 2: Accounts Receivable Schedule].
Every month, a student must achieve a certain balance or else, its excess will fall over due [See Table 1: Student Tuition Fee Balance Matrix].
So there are three conditions, the month we are currently in, the school level of the student and if his balance to date is greater than the matrix [over due] or less [no over due balance].
The Formula
=IF(B8<N9,IF(B8<M9,IF(B8<L9,IF(B8<K9,IF(B8<J9,IF(B8<I9,IF(B8<H9,IF(B8<G9,IF(B8<F9,IF(B8<E9,0,"FALSE"),E10),F10),G10),H10),I10),J10),K10),L10),M10)
worksheet-function microsoft-excel-2010
worksheet-function microsoft-excel-2010
edited Jan 25 at 2:31
angelofdev
929120
asked Jan 25 at 1:03
A.QueA.Que
11
edited Jan 25 at 2:31
angelofdev
929120
asked Jan 25 at 1:03
A.QueA.Que
11
edited Jan 25 at 2:31
angelofdev
929120
edited Jan 25 at 2:31
angelofdev
929120
edited Jan 25 at 2:31
angelofdev
929120
929120
asked Jan 25 at 1:03
A.QueA.Que
11
asked Jan 25 at 1:03
A.QueA.Que
11
asked Jan 25 at 1:03
A.QueA.Que
11
11
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
You seem to be asking two questions based on the details of your question. The first part is pretty clear from your title 'How to Shorten Formula IF with too many conditions?' and the image provided from Table 1.
The second part I do not understand what you are trying to achieve.
To simply answer your first question as per your title, the easiest way to shorten the IF formula is to use a combination of INDEX and MATCH functions to search the table for the value of the given month in Cell B8.
=INDEX(E9:N10,2,MATCH(DATE(YEAR(B8),MONTH(B8),DAY(1)),E9:N9,0))
answered Jan 25 at 1:54
angelofdevangelofdev
929120
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%2f1398160%2fhow-to-shorten-formula-if-with-too-many-conditions%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
You seem to be asking two questions based on the details of your question. The first part is pretty clear from your title 'How to Shorten Formula IF with too many conditions?' and the image provided from Table 1.
The second part I do not understand what you are trying to achieve.
To simply answer your first question as per your title, the easiest way to shorten the IF formula is to use a combination of INDEX and MATCH functions to search the table for the value of the given month in Cell B8.
=INDEX(E9:N10,2,MATCH(DATE(YEAR(B8),MONTH(B8),DAY(1)),E9:N9,0))
answered Jan 25 at 1:54
angelofdevangelofdev
929120
add a comment |
You seem to be asking two questions based on the details of your question. The first part is pretty clear from your title 'How to Shorten Formula IF with too many conditions?' and the image provided from Table 1.
The second part I do not understand what you are trying to achieve.
To simply answer your first question as per your title, the easiest way to shorten the IF formula is to use a combination of INDEX and MATCH functions to search the table for the value of the given month in Cell B8.
=INDEX(E9:N10,2,MATCH(DATE(YEAR(B8),MONTH(B8),DAY(1)),E9:N9,0))
answered Jan 25 at 1:54
angelofdevangelofdev
929120
add a comment |
You seem to be asking two questions based on the details of your question. The first part is pretty clear from your title 'How to Shorten Formula IF with too many conditions?' and the image provided from Table 1.
The second part I do not understand what you are trying to achieve.
To simply answer your first question as per your title, the easiest way to shorten the IF formula is to use a combination of INDEX and MATCH functions to search the table for the value of the given month in Cell B8.
=INDEX(E9:N10,2,MATCH(DATE(YEAR(B8),MONTH(B8),DAY(1)),E9:N9,0))
answered Jan 25 at 1:54
angelofdevangelofdev
929120
You seem to be asking two questions based on the details of your question. The first part is pretty clear from your title 'How to Shorten Formula IF with too many conditions?' and the image provided from Table 1.
The second part I do not understand what you are trying to achieve.
To simply answer your first question as per your title, the easiest way to shorten the IF formula is to use a combination of INDEX and MATCH functions to search the table for the value of the given month in Cell B8.
=INDEX(E9:N10,2,MATCH(DATE(YEAR(B8),MONTH(B8),DAY(1)),E9:N9,0))
answered Jan 25 at 1:54
angelofdevangelofdev
929120
answered Jan 25 at 1:54
angelofdevangelofdev
929120
answered Jan 25 at 1:54
angelofdevangelofdev
929120
answered Jan 25 at 1:54
angelofdevangelofdev
929120
929120
add a comment |
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%2f1398160%2fhow-to-shorten-formula-if-with-too-many-conditions%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
