Help with an existing RYGB Formula

LLaCosta

Below is the current formula in a RYGB column.

In the current set up, if the status is "not started" but the End date hasn't past, it will be yellow. Which is fine, however, I would like to update the formula to ALSO take into consideration that even if the status is "in progress", however, the % complete is less than the target % complete, instead of being green, it would show yellow. How can I update the formula to achieve this?

=IF([Parent (hidden)]@row > 0, IF(Status@row = "Completed", "Blue", IF((COUNT(COLLECT(CHILDREN(), CHILDREN(), "Blue")) = COUNT(CHILDREN([Parent (hidden)]@row))), "Blue", IF(COUNT(COLLECT(CHILDREN(), CHILDREN(), "Red")) > 0, "Red", IF(COUNT(COLLECT(CHILDREN(), CHILDREN(), "Yellow")) > 0, "Yellow", "Green")))), IF(Status@row <> "Completed", IF([Today (hidden)]$1 > [End Date]@row, "Red", IF(Status@row <> "In Progress", IF([Today (hidden)]$1 > [Start Date]@row, "Yellow"), "Green")), "Blue"))

Paul Newcome

I have seen most people prioritize the child rows to populate the parent rows. Something along the lines of "If there is at least one red then the parent is red, If there are no reds and at least one yellow then the parent is yellow. If there are no reds or yellows, then the parent is green."

You could also generate an average by assigning a number to each color, figuring in the average of the child rows, then converting that average back to the corresponding color.

I personally prefer the first method and tend to use that one the most.

Paul Newcome

Give this a try...

=IF([Parent (hidden)]@row > 0, IF(COUNTIFS(CHILDREN(), "Blue") = COUNT(CHILDREN()), "Blue", IF(CONTAINS("Red", CHILDREN()), "Red", IF(CONTAINS("Yellow", CHILDREN()), "Yellow", "Green"))), IF(Status@row <> "Completed", IF([Today (hidden)]$1 > [End Date]@row, "Red", IF([% Complete]@row < [Target % Complete]@row, "Yellow"), IF(Status@row <> "In Progress", IF([Today (hidden)]$1 > [Start Date]@row, "Yellow"), "Green")), "Blue"))

(I also rewrote the parent row portion of your formula)

LLaCosta

@Paul Newcome Thank you for giving it a shot! When I put the equation in, I got the error #incorrectargumentset

Paul Newcome

@LLaCosta

I broke it down and then put it back together to double check the continuity of it. I ended up misplacing a parenthesis. Give this one a try...

=IF([Parent (hidden)]@row > 0, IF(COUNTIFS(CHILDREN(), "Blue") = COUNT(CHILDREN()), "Blue", IF(CONTAINS("Red", CHILDREN()), "Red", IF(CONTAINS("Yellow", CHILDREN()), "Yellow", "Green"))), IF(Status@row <> "Completed", IF([Today (hidden)]$1 > [End Date]@row, "Red", IF([% Complete]@row < [Target % Complete]@row, "Yellow")), IF(Status@row <> "In Progress", IF([Today (hidden)]$1 > [Start Date]@row, "Yellow")), "Green"), "Blue"))

LLaCosta

@Paul Newcome Same error -- but you ROCK for giving it another try! : )

Paul Newcome

I have been taking a much closer look at it, and it seems as if you are trying to do too much we are approaching this the wrong way.

Can you list out exactly what you want the formula to do?

I think what keeps throwing me is exactly what the function is of the green and blue at the end of the formula.

LLaCosta

@Paul Newcome I am so sorry! I thought I answered this!!!! OOPS!

Lets see if I can answer this clearly. As I am thinking about it, maybe I am trying to factor in too many variables and I really only need to focus on % complete in comparison to Target % Complete...and let go of dates...

If:

% complete = 100%; Health = Blue

% complete is greater than 0% but less than 100% AND is greater than or equal to target % complete; Health = Green

% complete is greater than or equal to 0% and less than 100% BUT is less than target % complete; Health = Yellow

% complete is less than 100% AND the Target % Complete = 100% (the target % cell will actually be blank when @ 100%); Health = Red

% complete = 0% AND the Target % Complete = "Future"; Health = Blank or Green (either is acceptable)

This is a "nice to have" if there is there a way to also do this...

% complete is greater than 0% and less than 100% BUT is less than target % complete by more than half; Health = Red

Am I crazy?

: )

Paul Newcome

We can factor in dates. We just need to know exactly how they factor in. Based on the above though, give this a try...

=IF([% Complete]@row = 1, "Blue", IF(OR([% Complete]@row >= [Target % Complete]@row, AND([% Complete]@row = 0, [Target % Complete]@row = "Future")), "Green", IF(OR(ISBLANK([Target % Complete]@row), [% Complete]@row * 2 < [Target % Complete]@row), "Red", IF([% Complete]@row < [Target % Complete]@row, "Yellow"))))

LLaCosta

@Paul Newcome IT WORKED!!!!! Woot Woot!!!

: )

Final challenge though - LOL - @ the parent rows -- it ONLY looks at the rolled up % complete which can be problematic. If you have (1) child that is really far along and green, but 3 children that are red, the parent row could still be green which just looks wrong. (Hopefully that makes sense)

Overall thoughts?

Paul Newcome

I have seen most people prioritize the child rows to populate the parent rows. Something along the lines of "If there is at least one red then the parent is red, If there are no reds and at least one yellow then the parent is yellow. If there are no reds or yellows, then the parent is green."

You could also generate an average by assigning a number to each color, figuring in the average of the child rows, then converting that average back to the corresponding color.

I personally prefer the first method and tend to use that one the most.

LLaCosta

@Paul Newcome Thank you for all of your help -- you ROCK!

Paul Newcome

@LLaCosta Happy to help! 👍️

Please don't forget to mark the response(s) that best answered your original post as "helpful". That way others searching for a similar solution can know that one may be found here.

LLaCosta

@Paul Newcome ....been using the formula -- works great.

Hit a weird snag...if a task has a future date and I put in any % complete (even though I'm starting work early) it turns the health red?

Paul Newcome

@LLaCosta There are a couple of formulas floating around. Can you copy/paste your formula directly from the sheet? That way I know I am looking at the correct one.

LLaCosta

@Paul Newcome -- sure, here ya go

=IF([% Complete]@row = 1, "Blue", IF(OR([% Complete]@row >= [Target % Complete]@row, AND([% Complete]@row = 0, [Target % Complete]@row = "Future")), "Green", IF(OR(ISBLANK([Target % Complete]@row), [% Complete]@row * 2 < [Target % Complete]@row), "Red", IF([% Complete]@row < [Target % Complete]@row, "Yellow"))))

Paul Newcome

I want to focus on the BOLD portion...

=IF([% Complete]@row = 1, "Blue", IF(OR([% Complete]@row >= [Target % Complete]@row, AND([% Complete]@row = 0, [Target % Complete]@row = "Future")), "Green", IF(OR(ISBLANK([Target % Complete]@row), [% Complete]@row * 2 < [Target % Complete]@row), "Red", IF([% Complete]@row < [Target % Complete]@row, "Yellow"))))

Which date is a "future date" (start or finish)? What exactly makes the [Target % Complete]@row turn to "Future"?

If the [Target % Complete]@row = "Future", but you are making progress on the task anyway, would you want it to be green no matter the percentage (until it hits 100% and turns blue of course)?