# Schedule Health | Average RGYB symbols

Options
✭✭
edited 10/27/22

Hello community,

I have created a schedule health column in my schedule and used the following formula to calculate the row health:

=IF(Status@row = "On Hold", "Gray", IF(AND([% Complete]@row = 1, TODAY() >= [End Date]@row), "Green", IF(AND([% Complete]@row < 1, [End Date]@row = TODAY()), "Yellow", IF(AND([% Complete]@row < 1, [End Date]@row < TODAY(1)), "Red"))))

I would now like to calculate the overall health of the schedule based on the health of each row. For example, if 90 out of the 100 rows are green then the overall health would be green.

I have been given the below formula to use which is working but I don't think it is giving the correct overall health. Do I need to add a great than for it to calculate?

=IF(CONTAINS("Red", CHILDREN()), "Red", IF(CONTAINS("Yellow", CHILDREN()), "Yellow", IF(CONTAINS("Blue", CHILDREN()), "Blue", IF(CONTAINS("gray", CHILDREN()), "Gray", "Green"))))

Any help would be much appreciated! Thanks

Tags:

• ✭✭✭✭✭✭
Options

Hi Emily, give this one a try...

=IF(COUNTIF([Row Health]:[Row Health], "Gray") = COUNTIF([Row Health]:[Row Health], <>""), "Gray", IF(COUNTIF([Row Health]:[Row Health], "Green") / COUNTIF([Row Health]:[Row Health], <>"") >= 0.9, "Green", IF(COUNTIF([Row Health]:[Row Health], "Green") / COUNTIF([Row Health]:[Row Health], <>"") >= 0.75, "Yellow", "Red")))

Please let me know if that works for you!

Ryan

Ryan Sides

Come Say Hello!

• ✭✭✭✭✭✭
Options

Hi Emily, if the name of your Row Health column is "Row Health", the below formula will work. If it's not 90%, it shows "Yellow".

=IF(COUNTIF([Row Health]:[Row Health], "Green") / COUNTIF([Row Health]:[Row Health], <>"") >= 0.9, "Green", "Yellow")

Please let me know if that helps!

Ryan

Ryan Sides

Come Say Hello!

• ✭✭
Options

Hi Ryan,

Thank you so much for the formula - it worked!

I would now like to get a bit more complicated. Please could you help me with a formula to calculate the following?

90% + are Green rows = overall health is green

75% + are Green rows = overall health is Yellow

Anything lower than 75% Green = overall health is Red

If all rows are gray = overall health Gray

Thank you!

Emily

• ✭✭
Options

Sorry, I should have tagged you in my response @Ryan Sides :)

• ✭✭✭✭✭✭
Options

Hi Emily, give this one a try...

=IF(COUNTIF([Row Health]:[Row Health], "Gray") = COUNTIF([Row Health]:[Row Health], <>""), "Gray", IF(COUNTIF([Row Health]:[Row Health], "Green") / COUNTIF([Row Health]:[Row Health], <>"") >= 0.9, "Green", IF(COUNTIF([Row Health]:[Row Health], "Green") / COUNTIF([Row Health]:[Row Health], <>"") >= 0.75, "Yellow", "Red")))

Please let me know if that works for you!

Ryan

Ryan Sides

Come Say Hello!

• ✭✭
Options

Hi @Ryan Sides ,

Sorry for the slow reply. It worked perfectly - thanks so much for your help!

Emily

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!