Invalid Argument Set in IF statement
Hi,
The formula below is aimed at returning the date of a process step based on the current process step. It returns an invalid argument set when the process step is 7) Released. I have no idea where to start to troubleshoot. Any ideas welcome!
=IFERROR(IF([Request Status]@row = "5) On Hold", "On Hold", IF([Request Status]@row = "7) Cancelled", "Cancelled", IF(AND(ISBLANK([Process Step]@row), [Request Status]@row = "1) New"), [Request Date]@row, IF(AND(ISBLANK([Process Step]@row), NOT([Request Status]@row = "1) New")), "Status Not Provided", IF(ISBLANK([Process Step]@row), "", IF(AND([Process Step]@row = "1) Being Purified", NOT(ISBLANK([Request Date]@row))), [Request Date]@row, IF(AND([Process Step]@row = "2) Awaiting CRO", NOT(ISBLANK([Request Purified Date]@row))), [Request Purified Date]@row, IF(AND([Process Step]@row = "3) In Config/Dev", NOT(ISBLANK([JIRA Creation Date]@row))), [JIRA Creation Date]@row, IF(AND([Process Step]@row = "4) Internal Testing", "", IF(AND([Process Step]@row = "5) Customer Testing", NOT(ISBLANK([JIRA Actual UAT Date]@row))), [JIRA Actual UAT Date]@row, IF(AND([Process Step]@row = "6) Awaiting Release", NOT(ISBLANK([Customer Sign Off Date]@row))), [Customer Sign Off Date]@row, IF(AND([Process Step]@row = "7) Released", NOT(ISBLANK([JIRA Actual PROD Date]@row))), [JIRA Actual PROD Date]@row, TODAY()))))))))))))), "Error with formula")
Best Answer
-
I don't remember. Sorry. I deconstructed it an put it back together. There was an issue with syntax someplace in the middle and a paren or 2 out of place.
With complex formulas like this if you break each piece apart it makes identifying errors easier. I start by adding a hard return before each IF.
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
Answers
-
Try:
=IFERROR(IF([Request Status]@row = "5) On Hold", "On Hold", IF([Request Status]@row = "7) Cancelled", "Cancelled", IF(AND(ISBLANK([Process Step]@row), [Request Status]@row = "1) New"), [Request Date]@row, IF(AND(ISBLANK([Process Step]@row), NOT([Request Status]@row = "1) New")), "Status Not Provided", IF(ISBLANK([Process Step]@row), "", IF(AND([Process Step]@row = "1) Being Purified", NOT(ISBLANK([Request Date]@row))), [Request Date]@row, IF(AND([Process Step]@row = "2) Awaiting CRO", NOT(ISBLANK([Request Purified Date]@row))), [Request Purified Date]@row, IF(AND([Process Step]@row = "3) In Config/Dev", NOT(ISBLANK([JIRA Creation Date]@row))), [JIRA Creation Date]@row, IF([Process Step]@row = "4) Internal Testing", "", IF(AND([Process Step]@row = "5) Customer Testing", NOT(ISBLANK([JIRA Actual UAT Date]@row))), [JIRA Actual UAT Date]@row, IF(AND([Process Step]@row = "6) Awaiting Release", NOT(ISBLANK([Customer Sign Off Date]@row))), [Customer Sign Off Date]@row, IF(AND([Process Step]@row = "7) Released", NOT(ISBLANK([JIRA Actual PROD Date]@row))), [JIRA Actual PROD Date]@row, TODAY()))))))))))), "Error with formula")
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
Hi Mark,
It seems to be working. Thanks!
What did you change in the formula?
-
I don't remember. Sorry. I deconstructed it an put it back together. There was an issue with syntax someplace in the middle and a paren or 2 out of place.
With complex formulas like this if you break each piece apart it makes identifying errors easier. I start by adding a hard return before each IF.
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!