Invalid Argument Set in IF statement

Anne-Solene Monrouzeau

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")

Mark Cronk

Hi @Anne-Solene Monrouzeau ,

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

Mark Cronk

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

Anne-Solene Monrouzeau

Hi Mark,

It seems to be working. Thanks!

What did you change in the formula?

Mark Cronk

Hi @Anne-Solene Monrouzeau ,

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