Formula for automating Health status of project

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.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!