Health Formula

SmartSheetUser1014

Hello,

I am trying to apply a health formula to a project plan and getting a blocked and incorrect Argument Set. Any ideas for why? Formula is below:

=IF(Children@row = 0, IF(Status@row = "Complete", "Green", IF(OR(AND(ISDATE(Finish@row), Finish@row < TODAY(), Status@row = "On Hold", [At Risk]@row = true), "Red", IF(AND(ISDATE(Finish@row), Finish@row < TODAY(5)), "Yellow", IF(OR(Status@row = "", Status@row = "Not Started"), "Gray", "Green")))), IF(COUNTIF(CHILDREN(), "Gray") = COUNT(CHILDREN()), "Gray", IF(AND(COUNTIF(CHILDREN(), "Green") > COUNTIF(CHILDREN(), "Yellow"), COUNTIF(CHILDREN(), "Green") > COUNTIF(CHILDREN(), "Red")), "Green", IF(AND(COUNTIF(CHILDREN(), "Red") > COUNTIF(CHILDREN(), "Green"), COUNTIF(CHILDREN(), "Red") > COUNTIF(CHILDREN(), "Yellow")), "Red", "Yellow")))))

Pictures for column in the sheet also attached.

Danielle Arteaga

There are a few syntax issues in your formula that are causing the errors. If you can write out long-hand what the logic is for your status lights, it's easier to help.

Also, does your sheet actually have child rows in it? The first part of your formula reads as though it does not, but the latter part reads as though it does, and those two parts are not quite working together. I think that it may be the case that some of your rows have children and some do not, but it would be helpful to know a bit more about the structure of your sheet.

Brian_Richardson

Danielle is spot on, I often put formulas into Wordpad or something and break them out with indents to try to track complex nested IFs and ANDs.

I think your issue is the first IF(OR(AND( statement. You don't seem to use the OR, and you don't close it out before trying to provide the result "Red", so what's happening is that Smartsheet is confused and thinks that "Red" is another logical condition in your OR. But since it's not a logical condition, it's just text, then it's invalid and you're getting an error.

I didn't do the whole formula but if you follow this method you may discover other similar issues. It gets VERY difficult to parse a long string of formula text, believe me I know!

Another good approach is to write just the first IF statement and return some text like "Works". Then if it works with no error, replace "Works" with another IF statement, returning "Works" as a result. If that works then repeat…replacing the text with the next IF statement all the way through until you get it all written.

=IF(Children@row = 0,

IF(Status@row = "Complete", "Green",

IF(OR(

AND(

ISDATE(Finish@row),

Finish@row < TODAY(),

Status@row = "On Hold",

[At Risk]@row = true),

"Red",

IF(AND(

ISDATE(Finish@row),

Finish@row < TODAY(5)),

"Yellow",

IF(OR(

Status@row = "",

Status@row = "Not Started"),

"Gray",

"Green")))), ← track these closing parenthesis, I'm not sure the number is right given the OR issue.

etc