Assistance needed to provide a status if children tasks are not complete and / or < 100 % complete

I've tried several formulas and often receive back unparseable, blocked, incorrect, or circular

I need assistance with the parent tasks. For example, Where it says "Not Yet Started" and "In Progress - On Track" parent tasks I want the formula to indicate if all children tasks status = complete then mark parent complete, if children task = 0 % complete then status is "Not Started", if children task < 1 % complete then status is "In Progress - On Track"



Best Answer

  • markkrebs
    markkrebs ✭✭✭✭✭✭
    Answer ✓

    =IF([% complete Helper]@row = 0, "NOT YET Started", IF([% complete Helper]@row = 1, "Complete", "In Progress"))

    Helper column: % complete Helper

    =IF(Children@row > 0, AVG(CHILDREN()), [% complete]@row)

    Note: you only need to input the % complete on the bottom rows and make the % column a % format



Answers

  • markkrebs
    markkrebs ✭✭✭✭✭✭
    Answer ✓

    =IF([% complete Helper]@row = 0, "NOT YET Started", IF([% complete Helper]@row = 1, "Complete", "In Progress"))

    Helper column: % complete Helper

    =IF(Children@row > 0, AVG(CHILDREN()), [% complete]@row)

    Note: you only need to input the % complete on the bottom rows and make the % column a % format



  • Denera McCullough
    edited 02/25/21

    @markkrebs I used =IF([% complete]@row = 0, "Not Started", IF([% complete]@row = 1, "Complete", "In Progress - On Track")) and it worked like I wanted it to, then as soon as I added another column "% Not Started" for the formula (=IF(COUNT(CHILDREN([Tasks Name]@row)) > 0, COUNTIFS(CHILDREN(Status@row), "Not Started") / COUNTIFS(CHILDREN(Status@row), <>"N/A"))) then the column you assisted me with stopped working. Do you know why there's a glitch? It only changes if I change any of the children tasks marked complete (not all complete). It use to change to "In Progress" when there was any task in progress. Let know if that makes sense.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!