Need Help With Colored Health Status Formula

Matt RobertP

Hello,

I have a symbol column that automatically will assign a color (currently either red, yellow, or green) based on a due date in a column called "NEED ON SITE DATE". The formula looks at the "Fab Status % Complete (Needs Real Numbers)" column and will assign it a green dot if the percent complete is 100%(In this case 100% is represented by a "1", don't ask me why but it works....i think). The formula then goes on to also assign a green dot if its more than four weeks out, a yellow dot for two to four weeks out, and finally a red dot if it's less than two weeks out from the due date OR past the due date. The formula is working as it should from what I can tell. Here is that formula:

=IF([Fab Status % Complete (Needs Real Numbers)]@row = 1, "Green", (IF([Fab Status % Complete (Needs Real Numbers)]@row < 1, IF(NETDAYS(TODAY(), [NEED ON SITE DATE]@row) >= 42, "Green", IF(AND(NETDAYS(TODAY(), [NEED ON SITE DATE]@row) >= 14, NETDAYS(TODAY(), [NEED ON SITE DATE]@row) < 42), "Yellow", "Red")), "")))

Now this brings us to the change I want to make. I want to make it a gray circle if its past the due date and not 100% complete. I also have this formula working on it's own but I can't seem to get it to work when I go to add it to the above formula. So basically I just need someone to combine the two formulas together for me. I must just have a parenthesis missing or something. Here is the formula I want to add to the above formula:

=IF(AND([NEED ON SITE DATE]@row < TODAY(), [Fab Status % Complete (Needs Real Numbers)]@row < 1), "Gray", "")

Thanks in advance and feel free to double check my first formula there to make sure I have the week timing correct which I think I do....

-Matt

JamesB

@Matt RobertP

Happy to help. There may be a more simplified formula than the one below, and even in this some arguments could be removed, but it was just as easy to copy the first IF statement and change the qualifiers for the equal to expression.

=IF(MAX(COUNTIF(CHILDREN(), "Green"), COUNTIF(CHILDREN(), "Yellow"), COUNTIF(CHILDREN(), "Red"), COUNTIF(CHILDREN(), "Gray")) = COUNTIF(CHILDREN(), "Green"), "Green", IF(MAX(COUNTIF(CHILDREN(), "Green"), COUNTIF(CHILDREN(), "Yellow"), COUNTIF(CHILDREN(), "Red"), COUNTIF(CHILDREN(), "Gray")) = COUNTIF(CHILDREN(), "Yellow"), "Yellow", IF(MAX(COUNTIF(CHILDREN(), "Green"), COUNTIF(CHILDREN(), "Yellow"), COUNTIF(CHILDREN(), "Red"), COUNTIF(CHILDREN(), "Gray")) = COUNTIF(CHILDREN(), "Red"), "Red", IF(MAX(COUNTIF(CHILDREN(), "Green"), COUNTIF(CHILDREN(), "Yellow"), COUNTIF(CHILDREN(), "Red"), COUNTIF(CHILDREN(), "Gray")) = COUNTIF(CHILDREN(), "Gray"), "Gray"))))

This essentially gets the counts for the 4 colors and if the MAX number is Green 1st then Green, otherwise next if statement for Yellow, then Red, then Gray. The only issue here as with your formula above is if you have 2 color counts that are equal to each other, it is going to give color priority by layout in the formula. In this case these are the priorities.

Green, Yellow, Red, Gray

The priority can be changed by adjusting this section of each if statement to be the color you would prefer 1st in the case of 2 colors equaling the same amount.

COUNTIF(CHILDREN(), "Green"), "Green"

JamesB

@Matt RobertP

First, When you are working with Percent in SS, it sees them as Decimals, thus the reason that using a 1 in your formula for 100% works.

Now for your formula, try this... (I copied your two formulas from above and connected them)

=IF(AND([NEED ON SITE DATE]@row < TODAY(), [Fab Status % Complete (Needs Real Numbers)]@row < 1), "Gray",IF([Fab Status % Complete (Needs Real Numbers)]@row = 1, "Green", (IF([Fab Status % Complete (Needs Real Numbers)]@row < 1, IF(NETDAYS(TODAY(), [NEED ON SITE DATE]@row) >= 42, "Green", IF(AND(NETDAYS(TODAY(), [NEED ON SITE DATE]@row) >= 14, NETDAYS(TODAY(), [NEED ON SITE DATE]@row) < 42), "Yellow", "Red")), ""))))

Paul Newcome

Something to keep in mind with nested IF statements is that it will stop running on the first "true" statement. This means if it makes it to (for example) the 3rd IF, we can assume that the first two IFs are false. We can leverage this to simplify your formula a bit by not requiring the AND functions. There are also a couple of other adjustments we can make to simplify things a little bit. This should also work for you:

=IF(OR([Fab Status % Complete (Needs Real Numbers)]@row = 1, [NEED ON SITE DATE]@row>= TODAY(42)), "Green", IF([NEED ON SITE DATE]@row>= TODAY(14), "Yellow", IF([NEED ON SITE DATE]@row>= TODAY(), "Red", "Gray")))

Since the first IF says "Green" for rows that are 100% or more than 42 days in the future, we can assume that if we make it past those, both must be false. To output a "Yellow", it is assumed that it is not 100% and is also less than 42 days in the future. This means we don't have to include those arguments for the "Yellow" output. We only need to enter the additional argument for that color. Same goes for "Red". Less than 100% and within 42 days and within 14 days are already assumed, so all we need to say is greater than or equal to today. Finally, since we have already output all of the other colors with the other arguments, we can just use "Gray" as the "value if false" piece because anything that makes it that far is not 100% and is less than today (in the past).

Matt RobertP

@JamesB Awesome that worked thank you!

One last bit of help: On the parents rows I have a formula to give the average color for all child rows associated with the parent row. Can you add in gray for me?

=IF(COUNTIF(CHILDREN(), "Green") > COUNTIF(CHILDREN(), "Red"), IF(COUNTIF(CHILDREN(), "Green") > COUNTIF(CHILDREN(), "Yellow"), "Green", "Yellow"), IF(COUNTIF(CHILDREN(), "Red") > COUNTIF(CHILDREN(), "Yellow"), "Red", "Yellow"))

JamesB

@Matt RobertP

Happy to help. There may be a more simplified formula than the one below, and even in this some arguments could be removed, but it was just as easy to copy the first IF statement and change the qualifiers for the equal to expression.

=IF(MAX(COUNTIF(CHILDREN(), "Green"), COUNTIF(CHILDREN(), "Yellow"), COUNTIF(CHILDREN(), "Red"), COUNTIF(CHILDREN(), "Gray")) = COUNTIF(CHILDREN(), "Green"), "Green", IF(MAX(COUNTIF(CHILDREN(), "Green"), COUNTIF(CHILDREN(), "Yellow"), COUNTIF(CHILDREN(), "Red"), COUNTIF(CHILDREN(), "Gray")) = COUNTIF(CHILDREN(), "Yellow"), "Yellow", IF(MAX(COUNTIF(CHILDREN(), "Green"), COUNTIF(CHILDREN(), "Yellow"), COUNTIF(CHILDREN(), "Red"), COUNTIF(CHILDREN(), "Gray")) = COUNTIF(CHILDREN(), "Red"), "Red", IF(MAX(COUNTIF(CHILDREN(), "Green"), COUNTIF(CHILDREN(), "Yellow"), COUNTIF(CHILDREN(), "Red"), COUNTIF(CHILDREN(), "Gray")) = COUNTIF(CHILDREN(), "Gray"), "Gray"))))

This essentially gets the counts for the 4 colors and if the MAX number is Green 1st then Green, otherwise next if statement for Yellow, then Red, then Gray. The only issue here as with your formula above is if you have 2 color counts that are equal to each other, it is going to give color priority by layout in the formula. In this case these are the priorities.

Green, Yellow, Red, Gray

The priority can be changed by adjusting this section of each if statement to be the color you would prefer 1st in the case of 2 colors equaling the same amount.

COUNTIF(CHILDREN(), "Green"), "Green"

Matt RobertP

Worked again, you're the best thanks!