Nested IF statement

Hello - I need some help trouble shooting a nested IF statement.

In our project plans, we have a column that show the decision made for a specific phase of development. The are Go, NoGo, Transferred and Terminated decisions. I have recently added the Transferred decision. These decisions are driven by multiple factors in the sheet. If a project is Transferred or Terminated, it will always be the last phase reported in the project plan. I cannot get the "Transferred" to work, which I think is the result of incorrect parenthesis in a nested IF statement - but I have stared at this for quite a while and can't see the problem. The last part of the statement drives the Go and NoGo decisions via a Index Collect from a reference sheet. Thanks for any help. The formula is below

=IFERROR(IF(AND([Current Phase]@row = "Transferred", [End Date]@row = MAX(COLLECT([End Date]:[End Date], [Phase (Y/N)]:[Phase (Y/N)], 1, [Current Phase Identifier]:[Current Phase Identifier], 0))), "Transferred", IF(AND([Current Phase]@row = "Terminated", [End Date]@row = MAX(COLLECT([End Date]:[End Date], [Phase (Y/N)]:[Phase (Y/N)], 1, [Current Phase Identifier]:[Current Phase Identifier], 0))), "NoGo", IF([Phase (Y/N)]@row = 1, INDEX(COLLECT({Result}, {Phase Status}, [Phase Status]@row , {Project Status}, [Current Project Status]@row ), 1))), ""))

Find more posts tagged with

Accepted answers

All comments

Paul Newcome

Are you getting an error message, or is it outputting somethign unexpected? Are you able to provide screenshots for context?

TCJ

Hello Paul I get an "Incorrect Argument Set"

TCJ

Here is a screen shot. I have only been working on the formula in the cells that says Incorrect Argument. It will eventually be a column formula (if it works). The formula that is in the cells that have "Go" is the following, which works just fine.

=IFERROR(IF(AND([Current Phase]@row = "Terminated", [End Date]@row = MAX(COLLECT([End Date]:[End Date], [Phase (Y/N)]:[Phase (Y/N)], 1, [Current Phase Identifier]:[Current Phase Identifier], 0))), "NoGo", IF([Phase (Y/N)]@row = 1, INDEX(COLLECT({Result}, {Phase Status}, [Phase Status]@row , {Project Status}, [Current Project Status]@row ), 1))), "")

Paul Newcome

And which output is expected from the above? "NoGo" or the INDEX/COLLECT result?

TCJ

Thanks for your help - if the last phase (as in the screenshot) is Transferred, I would like the out put from the original formula I posted (not the one in the post with the screenshot) to return "Transferred" not from the Index Collect.

Paul Newcome

So the row in your screenshot that says "INCORRECT ARGUMENT SET" should be using the formula from your original post and outputting "Transferred"?

What happens if you use this:
=IF(AND([Current Phase]@row = "Transferred", [End Date]@row = MAX(COLLECT([End Date]:[End Date], [Phase (Y/N)]:[Phase (Y/N)], 1, [Current Phase Identifier]:[Current Phase Identifier], 0))), "Transferred")

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.