Status Formula is not calculating correctly for parent tasks

CrystalJ_Medtronic

I was trying to align my Health and Status columns, but the status formula is acting wonky.. (there's a technical term for ya! 😂) First a HUGE disclaimer. I'm not not new to Smartsheet, but I am new to this particular sheet. I inherited it, and the person who created this formula is out on leave until February.

Here's my Health column formula:

=IF(OR([Task Name]@row = "Initiative/Program Overview", Program@row = "Initiative/Program Overview"), "", IF(OR(Status@row = "On Track", Status@row = "Completed On Time"), "Green", IF(OR(Status@row = "At Risk", Status@row = "Completed Late"), "Yellow", IF(Status@row = "Behind", "Red", "Gray"))))

Here's my Status column formula

=IF(OR([Task Name]@row = "Initiative/Program Overview", Program@row = "Initiative/Program Overview"), "", IF(Parent@row = 1, IFERROR(IF(ISBLANK([Start Date]@row), "Not Started", IF(AND(ISBLANK([Actual End Date]@row), [Start Date]@row > TODAY()), "Not Started", IF(AND([Actual End Date]@row <= [Target End Date]@row, [% Completion]@row = 1), "Completed On Time", IF(AND([Actual End Date]@row > [Target End Date]@row, [% Completion]@row = 1), "Completed Late", IF(COUNTIF(CHILDREN(Health@row), "Green") / COUNTIFS(CHILDREN(Health@row), <>"Gray") > 0.79, "On Track", IF(COUNTIF(CHILDREN(Health@row), "Yellow") / COUNTIFS(CHILDREN(Health@row), <>"Gray") > 0.19, "At Risk", IF(COUNTIF(CHILDREN(Health@row), "Red") / COUNTIFS(CHILDREN(Health@row), <>"Gray") > 0.19, "Behind"))))))), ""), IF(ISBLANK([Start Date]@row), "Not Started", IF(AND(ISBLANK([Actual End Date]@row), [Start Date]@row > TODAY()), "Not Started", IF(AND([Target End Date]@row < TODAY(), ISBLANK([Actual End Date]@row)), "Behind", IF(AND(ISBLANK([Actual End Date]@row), [At Risk]@row = 1), "At Risk", IF(AND([Start Date]@row < TODAY(), [On Track]@row = 0, [At Risk]@row = 0, Behind@row = 0), "Check Applicable Status Checkbox", IF(AND(ISBLANK([Actual End Date]@row), [On Track]@row = 1), "On Track", IF(AND(ISBLANK([Actual End Date]@row), [At Risk]@row = 1), "At Risk", IF(AND(ISBLANK([Actual End Date]@row), Behind@row = 1), "Behind", IF([Actual End Date]@row <= [Target End Date]@row, "Completed On Time", IF([Actual End Date]@row > [Target End Date]@row, "Completed Late"))))))))))))

What's happening is that the parent tasks are blank (see screenshot), and I don't understand what I need to do to fix this.

Christian G.

At first glance, I would check if it is the "IFERROR" that is triggered. Can you remove it and see that Error is thrown ?

CrystalJ_Medtronic

When I remove

IFERROR(IF(ISBLANK([Start Date]@row), "Not Started", 

I get this error

Christian G.

Remove the text in bold only:

=IF(OR([Task Name]@row = "Initiative/Program Overview", Program@row = "Initiative/Program Overview"), "", IF(Parent@row = 1, IFERROR(IF(ISBLANK([Start Date]@row), "Not Started", IF(AND(ISBLANK([Actual End Date]@row), [Start Date]@row > TODAY()), "Not Started", IF(AND([Actual End Date]@row <= [Target End Date]@row, [% Completion]@row = 1), "Completed On Time", IF(AND([Actual End Date]@row > [Target End Date]@row, [% Completion]@row = 1), "Completed Late", IF(COUNTIF(CHILDREN(Health@row), "Green") / COUNTIFS(CHILDREN(Health@row), <>"Gray") > 0.79, "On Track", IF(COUNTIF(CHILDREN(Health@row), "Yellow") / COUNTIFS(CHILDREN(Health@row), <>"Gray") > 0.19, "At Risk", IF(COUNTIF(CHILDREN(Health@row), "Red") / COUNTIFS(CHILDREN(Health@row), <>"Gray") > 0.19, "Behind"))))))), ""), IF(ISBLANK([Start Date]@row), "Not Started", IF(AND(ISBLANK([Actual End Date]@row), [Start Date]@row > TODAY()), "Not Started", IF(AND([Target End Date]@row < TODAY(), ISBLANK([Actual End Date]@row)), "Behind", IF(AND(ISBLANK([Actual End Date]@row), [At Risk]@row = 1), "At Risk", IF(AND([Start Date]@row < TODAY(), [On Track]@row = 0, [At Risk]@row = 0, Behind@row = 0), "Check Applicable Status Checkbox", IF(AND(ISBLANK([Actual End Date]@row), [On Track]@row = 1), "On Track", IF(AND(ISBLANK([Actual End Date]@row), [At Risk]@row = 1), "At Risk", IF(AND(ISBLANK([Actual End Date]@row), Behind@row = 1), "Behind", IF([Actual End Date]@row <= [Target End Date]@row, "Completed On Time", IF([Actual End Date]@row > [Target End Date]@row, "Completed Late"))))))))))))

CrystalJ_Medtronic

Okay.. So that made a change ( I hope this means this is progress.. 🤣)

CrystalJ_Medtronic

I want to thanks folk for the responses..

Upon reflection, I've decided to simplify things a LOT with regards to this sheet and this formula in particular. Since the original author is not around to explain this complex formula (or WHY a formula/process this complex was needed), I made an executive decision to simplify this WHOLE daggone process and get my WHOLE LIFE BACK 😂

(See: https://community.smartsheet.com/discussion/comment/350533)