RYG Formula Help

Options
BradFlynn
BradFlynn
edited 12/09/19 in Formulas and Functions

My formula keeps coming up as Unparsable and I cannot find the issue. Can anyone spot the issue? 

=IF(COUNTIF(CHILDREN(), "Green") / COUNT(CHILDREN()) > 0.6, "Green", IF(COUNTIF(CHILDREN(), "Yellow") / COUNT(CHILDREN()) > 0.4, "Yellow", IF(COUNTIF(CHILDREN(), "Red") / COUNT(CHILDREN()) > 0.2, "Red", 0))), IF(OR(ISBLANK([Due Date]1), ISBLANK([% Completed]1)), 0, IF(AND([Due Date]1 - TODAY() < 0, [% Completed]1 < 1), "Red", IF(AND([Due Date]1 - TODAY() <= 7, [% Completed]1 <= 0.5), "Red", IF(AND([Due Date]1 - TODAY() <= 21, [% Completed]1 <= 0.5), "Yellow", IF(AND(AND([Due Date]1 - TODAY() <= 7, [% Completed]1 > 0.5), [% Completed]1 <= 0.99), "Yellow", IF([% Completed]1 = 1, "Green", IF(AND(AND([Due Date]1 - TODAY() > 7, [% Completed]1 > 0.5), [% Completed]1 <= 0.99), "Green", IF(AND([Due Date]1 - TODAY() >= 20, [% Completed]1 < 1), "Green", "error")))))))))))

Tags:

Comments

  • Chris McKay
    Chris McKay ✭✭✭✭✭✭
    Options

    Hiya Brad,

    Looks like you have a couple of AND statements nested within each other that will cause issues as a quick glance shows they are not being closed properly.

    As you are essentially closing things off after "Red", 0))) at the beginning of your 4th line above, it seems like you might have some extra close brackets at the end of your formula causing issues.

    I can tell you all your issues are from the beginning of your 4th line onwards.

    Kind regards,

    Chris McKay

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!