Status is blank when "% Completion" column is less than 100%

Options
CrystalJ_Medtronic
CrystalJ_Medtronic ✭✭✭✭
edited 01/30/23 in Formulas and Functions

I inherited a sheet with an existing formula to calculate the task status. It works just fine UNLESS the "% Completion" column is less than 100%. I am fully stumped how to fix it. Hoping one of the many Smart people here can help..

=IF(ISBLANK([Start Date]@row), "Not Planned", IF(AND([% Completion]@row = 0, [Start Date]@row > TODAY()), "Not Started", IF(AND(ISBLANK([Actual End Date]@row), SUMIF(CHILDREN([Start Date]@row), <TODAY(), CHILDREN([% Completion]@row)) + 0.1 >= SUMIF(CHILDREN([Start Date]@row), <TODAY(), CHILDREN([% Duration Used]@row))), "On Track", IF(AND(ISBLANK([Actual End Date]@row), SUMIF(CHILDREN([Start Date]@row), <TODAY(), CHILDREN([% Completion]@row)) + 0.1 < SUMIF(CHILDREN([Start Date]@row), <TODAY(), CHILDREN([% Duration Used]@row))), "Behind", IF(AND([% Completion]@row = 1, [Actual End Date]@row > [Target End Date]@row), "Completed Late", IF(AND([% Completion]@row = 1, [Actual End Date]@row <= [Target End Date]@row), "Completed On Time"))))))



Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!