Schedule Health | Average RGYB symbols

Emily S

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

Ryan Sides

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

@Emily S

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

Emily S

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

Emily S

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

Ryan Sides

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

Emily S

Hi @Ryan Sides ,

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

Emily