Health Icon Formula Help

jesshuntley

Hi All -

Longtime lurker, first time poster.

I am attempting to add a Health Column to a smartsheet created by a colleague.

I have been in the forums checking answers to similar, and tried to kluge my own formula together, but I've either missed the logic or I'm trying to do too much.

What i'm trying to achieve

rollup logic for parent/child

gray if there is no date assigned in either start date or final deadline OR % complete is 0 OR Status is "On Hold" or "Cancelled".

Green if % Complete is 100%,

Red if % Complete is less than 25% OR Final Deadline is Today.

Yellow if % Complete is less than 75% AND Final Deadline is within a week from today

formula text here:

=IF(COUNTIFS(CHILDREN(), "Red") > 0, "Red", IF(COUNTIFS(CHILDREN(), "Yellow") > 0, "Yellow", IF(COUNTIFS(CHILDREN(), "Green") > 0, "Green", "Gray"))IF(TODAY() > [Final Deadline]@row, "Red", IF(TODAY(7) > = [Final Deadline]@row, IF([% Complete]@row < 0.75, "Yellow", "Green")), IF([% Complete]@row < 0.25, "Red", IF([% Complete]@row < 1, "Yellow", "Green")), IF(OR(ISBLANK([Final Deadline]@row), ISBLANK([% Complete]@row), (Status@row = "On Hold"), (Status@row = "Cancelled"), "Gray")))

Screenshot below

any advice on tweaks to make the formula work?

Thank you in advance.

Paul Newcome

Give this a try:

=IF(COUNTIFS(CHILDREN(), "Red") > 0, "Red", IF(COUNTIFS(CHILDREN(), "Yellow") > 0, "Yellow", IF(COUNTIFS(CHILDREN(), "Green") > 0, "Green", IF(COUNTIFS(CHILDREN(), "Gray") > 0, "Gray", IF(OR([Final Deadline]@row = "", [% Complete]@row = "", Status@row = "On Hold", Status@row = "Cancelled"), "Gray", IF(OR([Final Deadline]@row < TODAY(), [% Complete]@row < 0.25), "Red", IF(AND([% Complete]@row < 0.75, [Final Deadline]@row < TODAY(7)), "Yellow", "Green")))))))

Paul Newcome

Give this a try:

=IF(COUNTIFS(CHILDREN(), "Red") > 0, "Red", IF(COUNTIFS(CHILDREN(), "Yellow") > 0, "Yellow", IF(COUNTIFS(CHILDREN(), "Green") > 0, "Green", IF(COUNTIFS(CHILDREN(), "Gray") > 0, "Gray", IF(OR([Final Deadline]@row = "", [% Complete]@row = "", Status@row = "On Hold", Status@row = "Cancelled"), "Gray", IF(OR([Final Deadline]@row < TODAY(), [% Complete]@row < 0.25), "Red", IF(AND([% Complete]@row < 0.75, [Final Deadline]@row < TODAY(7)), "Yellow", "Green")))))))

jesshuntley

Thank you @Paul Newcome

I'm so glad you replied; your answers to complex formulas in the community are always helpful!

the formula worked better than my initial attempts, but I found a bit of weirdness related to dates/no dates and % complete. (this is an issue when taking over another's project and the schedule is incomplete.)

Screenshot below showing new formula in draft template. it's pulling gray into the health column even when the % complete is above 0 (which should be not gray) and when the % complete is 100% (complete) which should be green.

can you assist?

Paul Newcome

That is coming from this part of your original post:

"gray if there is no date assigned in either start date or final deadline OR % complete is 0 OR Status is "On Hold" or "Cancelled"."

jesshuntley

ah, okay. let me play around a bit more with the logic and return with clarity in the request.
I appreciate you!