Add a specific condition to an existing formula
Hi,
I am trying to ad a specific condition
IF(TD<[@[DUE DATE]]+7,"PIPELINE"
to an existing formula. However, the existing condition
IF(TD<[@[DUE DATE]],"CURRENT"
is interfering with the addition, and I cannot figure out where I am going wrong. I tried all sort of combinations.
The complete formula is:
=IFERROR(IF(OR([@[INVOICE AMOUNT]]="",[@[INVOICE DATE]]="",[@[DUE DATE]]="",[@[DUE DATE]]<[@[INVOICE DATE]]),"ERROR",IF([@[OUTSTANDING AMOUNT]]=0,"PAID", IF([@[OUTSTANDING AMOUNT]]>0,IF(TD<[@[DUE DATE]],"CURRENT",IF(TD=[@[DUE DATE]],"DUE TODAY",IF(TD>[@[DUE DATE]],"PAST DUE"))),IF([@[OUTSTANDING AMOUNT]]<0,"OVERPAID")))),"")
I'd appreciate your input and help in this matter.
Thank you :)
Marion
Answers
-
You would want to put the "CURRENT" IF before the "PIPELINE" IF.
=IFERROR(IF(OR([@[INVOICE AMOUNT]]="",[@[INVOICE DATE]]="",[@[DUE DATE]]="",[@[DUE DATE]]<[@[INVOICE DATE]]),"ERROR",IF([@[OUTSTANDING AMOUNT]]=0,"PAID", IF([@[OUTSTANDING AMOUNT]]>0,IF(TD<[@[DUE DATE]],"CURRENT",IF(TD<[@[DUE DATE]]+7,"PIPELINE", IF(TD=[@[DUE DATE]],"DUE TODAY",IF(TD>[@[DUE DATE]],"PAST DUE")))),IF([@[OUTSTANDING AMOUNT]]<0,"OVERPAID")))),"")
-
Sorry for the duplication in questions.
@Paul - I tried using this technique, but get the error "too many arguments for this function"
-
Can you explain the intended logic for this formula? How exactly do you want this to work?
-
This particular column indicates the payment "Status" of an invoice.
Right now I have everything that is TD< indicating "Current".
What I am trying to do is label everything "Current" that's within 7 days of TD, anything due in more than 7 days I want to label "Pipeline"
I think I am making an error in my thought process on the integration of the additional format
-
I understand what you are trying to add. If you can explain the logic behind each portion, it will help me determine the correct placement of the addition.
-
I'm not sure I understand what you mean by logic, sorry
-
Explain exactly how the formula is supposed to work.
If "this" is true then output "THIS".
If "that" is less than 7 and the date is in the past then output "PAST".
So on and so forth.
If you can explain exactly how the entire formula is supposed to work, it may help us determine exactly where your addition needs to fit in.
-
I can send you the sheet, I just need to take out all the input data
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!