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!

jesshuntley

Hi @Paul Newcome - I'm back with real-life, redacted smartsheet examples of the formula in action, and where I need help. Everything is showing Red, even things that have been completed, which is frustrating management and project teams.

I think I had the logic incorrect to start with, so I'm asking your continued help here.

using a RYGG health ball. what I would like to have occur:

Green when complete, regardless of when it was completed, or if it was completed past the "Final Deadline" (Due Date). (Meaning, if was Red, but % Complete has been changed to 100%, the health ball should change to Green)

Red if % Complete is less than 25% OR Final Deadline is Today's Date or in the past.

Yellow if % Complete is less than 75% OR Final Deadline is within 7 days from today's date.

Change in the gray logic - Gray if there is no date assigned in either start date or final deadline OR % complete is 0 OR Status is NOT STARTED "On Hold" or "Cancelled".

Status column choices are: NOT STARTED, IN PROGRESS, COMPLETE, NOT APPLICABLE, ON HOLD, CANCELLED

Can you help with an updated formula?

Thank you!

Paul Newcome

It is because you have your "Red" argument coming before the "Green". Nested IFs read from left to right and stop on the first true argument. This means it is getting to the argument of the date being in the past and outputting the appropriate "Red". You will want to write your IFs in order of highest priority to lowest priority. For example, if something being "Complete" should be "Green" is more important than a date being in the past, then the argument that outputs "Green" when something is complete should come before the "Red" argument.

jesshuntley

@Paul Newcome

Thanks for the advice on the formula logic.

as this is the current formula:

=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")))))))

would the logical restating be:

=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 = "Not Started"), "Gray", IF(OR([% Complete]@row =1.0), Status@row = "Complete")), "Green", IF(AND([% Complete]@row < 0.75, [Final Deadline]@row < TODAY(7)), "Yellow"), IF(OR([Final Deadline]@row < TODAY(), [% Complete]@row < 0.25)), "Red"))))))

Paul Newcome

That should work.

jesshuntley

Coming up as "#incorrect argument set"

=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 = "Not Started"), "Gray", IF(OR([% Complete]@row = 1), Status@row = "Complete")), "Green", IF(AND([% Complete]@row < 0.75, [Final Deadline]@row < TODAY(7)), "Yellow"), IF(OR([Final Deadline]@row < TODAY(), [% Complete]@row < 0.25)), "Red"))))

Paul Newcome

Looks like maybe a couple of closing parenthesis that need removed. Try removing the one after [% Complete]@row = 1 as well as one of them after Status@row = "Complete".

jesshuntley

revised to

=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 = "Not Started"), "Gray", IF(AND([% Complete]@row = 1, Status@row = "Complete"), "Green", IF(OR([% Complete]@row < 0.75, [Final Deadline]@row < TODAY(7)), "Yellow", IF(OR([Final Deadline]@row < TODAY(), [% Complete]@row < 0.25)), "Red")))))))

ERROR: #INCORRECT ARGUMENT SET

Paul Newcome

I missed the extra closing parenthesis that needs removed from after [% Complete]@row < 0.25.

You may also want to switch your final red and yellow IFs around. 0.01 is less than 0.75, so it will stop there and output yellow. It will never get to the following red. Same logic for the dates. A date in the past is technically less than 7 days in the future, so you're going to get stopped on yellow and never make it to the final red.

jesshuntley

@Paul Newcome

THANK YOU!

Playing around with it now. I appreciate it.

Paul Newcome

Happy to help. 👍️