Complicated "IF" function

Question

Hi -

Need some help with a function that has a lot of different criteria.

Here is my current formula, which is returning #INCORRECT ARGUMENT SET:

=IF([Project Type]@row = "TI", AND(ISDATE([LL Dwgs Received]@row), [Model - Complete/Posted Status]@row = "Complete"), "Complete", IF(AND(ISDATE([LL Dwgs Received]@row), OR([Model - Complete/Posted Status]@row = "Not Started", [Model - Complete/Posted Status]@row = "In Progress")), "In Progress", "Not Started"))

What i'm trying to calculate is that when a project is a TI, LL Dwgs Rec'd is a date and Model - Complete is "complete", then output "complete" but if the project is a TI, LL Dwgs Rec'd is a date and Model - Complete is "Not Started" or "In Progress" then i need it to return "In Progress".

Once i figure out the formula for the TI projects, i will add to the end a similar function for Reno projects.

LeelaLodhi03 · Answer

Hi @aecross,

You’re getting the error because of a missing closing parenthesis in the third IF statement (the Reno condition). The AND() function isn’t fully closed before "In Progress".

Try this

=IF(AND([Project Type]@row = "TI", ISDATE([LL Dwgs Received]@row), [Model - Complete/Posted Status]@row = "Complete"), "Complete",

IF(AND([Project Type]@row = "TI", ISDATE([LL Dwgs Received]@row), OR([Model - Complete/Posted Status]@row = "Not Started", [Model - Complete/Posted Status]@row = "In Progress")), "In Progress",

IF(AND([Project Type]@row = "Reno", OR(ISDATE([Survey Date]@row),ISDATE([DD Start Date]@row)), OR([Budget Received Status]@row = "Not Started", [Test Fit Budget Received Status]@row = "In Progress", [Test Fit Budget Received Status]@row = "N/A")), "In Progress",IF(AND([Project Type]@row = "Major Reno", [As-Built Model Complete Status]@row = "Complete"), "Complete", "Not Started"))))

I agree with @heyjay, breaking the formula into smaller blocks and testing them individually can help you quickly find the error.

heyjay · Answer

Hello, Can you test ONLY this part of your formula. There might be an issue with the AND OR logic here somewhere:

IF(AND([Project Type]@row = "Reno", OR(ISDATE([Survey Date]@row), ISDATE([DD Start Date]@row)), OR([Budget Received Status]@row= "Not Started", [Test Fit Budget Received Status]@row = "In Progress", [Test Fit Budget Received Status]@row ="N/A"), "In Progress",

I would do the same for the other IF statement, I will test them separately before nesting them to make sure each section is working OK.

aecross · Answer

Thanks, @heyjay and @LeelaLodhi03 - these were both helpful!

This worked for the first portion of my formula (bolded and italicized below), however i need to add another a part to account for if the project type is Reno.  When i add the criteria for Reno, i get the #UNPARSABLE error.  Any thoughts on how to fix?

=IF(AND([Project Type]@row = "TI", ISDATE([LL Dwgs Received]@row), [Model - Complete/Posted Status]@row = "Complete"), "Complete", IF(AND([Project Type]@row = "TI", ISDATE([LL Dwgs Received]@row), OR([Model - Complete/Posted Status]@row = "Not Started", [Model - Complete/Posted Status]@row = "In Progress")), "In Progress", IF(AND([Project Type]@row = "Reno", OR(ISDATE([Survey Date]@row), ISDATE([DD Start Date]@row)), OR([Budget Received Status]@row= "Not Started", [Test Fit Budget Received Status]@row = "In Progress", [Test Fit Budget Received Status]@row ="N/A"), "In Progress", IF(AND([Project Type]@row = "Major Reno", [As-Built Model Complete Status]@row = "Complete"), "Complete", "Not Started")