Formula for automating Health status of project

rmpedro68986 · 11/13/18

I have two formula questions.

1. I have Parent rows with a rollup formula created that takes the avg of the RYG balls of its childreen and creates a status =IF(COUNTIF(CHILDREN(), "Red") > 0, "Red", IF(COUNTIF(CHILDREN(), "Yellow") > 0, "Yellow", IF(COUNTIF(CHILDREN(), "Green") > 0, "Green", "Gray"))) ....

I also have a formula for the child rows to Formulate RYG balls based on task start and end date =IF(Completed3 = 1, "Gray", IF([Planned Finished Date]3 < TODAY(+2), "Red", IF(AND([Planned Finished Date]3 >= TODAY(+2), [Planned Finished Date]3 < TODAY(+7)), "Yellow", IF([Planned Finished Date]3 >= TODAY(+7), "Green", "Nope"))))

I would like for these to both be combined that way smartsheets first identifies if the row is a parent or child and then runs the formula to code the ball. right now I am having to manually copy one of the two formulas over based on what type of row is being added when creating new tasks on the sheet. I would just like it to be the same formula applied to the whole column but cant get it to work when I pair them together.

2. I'm trying to use a formula to automate health of a task within a project. After the RYG balls are formulated I would like to add an additional column that has:

- When RYG for a task is Red or Yellow status, the column updates to "Behind Schedule"

- When RYG is Green, "On Schedule"

- When RYG is Gray, "Completed"

any and all help Is greatly appreciated! thank you.