RYG Balls changing with Children Rows

Hello,

I have an *awesome* formula that changes RYG balls of parent rows as the children rows change. The formula is below:

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

My issue is that when the children rows have not yet selected a RYG ball, the parent ball shows up as Yellow. Is there a way to make it just be blank if the rows below it are blank? Or at least, Green? The "Yellow" can have a negative connotation and I do not want a Project Manager thinking a new project is in the Yellow when they are given the SmartSheet template.

Thank you!

Find more posts tagged with

Formulas

Comments

John Sauber

With that formula, the parent doesn't default to yellow, it's still calculating based on the criteria you set in your IF() and AND() statements. Go ahead and see for yourself by deleting some of the RYG data in the child rows and watch the parent change according to the rules you set.

If you want the parent to default to blank until all children have been filled out, then do this (I only added another IF() qualifier at the begining to test if any child rows are blank):

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

You can default to something other than "blank" of course, and you might even consider using RYG(B/G), and make it Blue or Gray (after changing the symbol type, of course) if it's not all filled out yet. Just a thought, but your situation is yours to consider. That would look like:

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

Madeline Brannen

John,

Thank you for your response! The problem is, I don't want the parent to default to blank until all the children to be filled. I do like the fact that it will show and change as the children below it change.

I want the parent to be blank when there are NO children filled, but a gray ball will do nicely also! I've attached a picture of what I mean. The yellow scares people when the tasks below the parent haven't even begun yet.

John Sauber

You just need to do a comparison first, which I have done below. I am assumng the name [Primary Column] is the name you have for the column where those tasks are listed.

=IF(COUNTIF(CHILDREN(), "") = COUNT(CHILDREN([Primary Column]1)), "Gray", IF(AND(COUNTIF(CHILDREN(), "Green") > COUNTIF(CHILDREN(), "Yellow"), COUNTIF(CHILDREN(), "Green") > COUNTIF(CHILDREN(), "Red")), "Green", IF(AND(COUNTIF(CHILDREN(), "Red") > COUNTIF(CHILDREN(), "Green"), COUNTIF(CHILDREN(), "Red") > COUNTIF(CHILDREN(), "Yellow")), "Red", "Yellow")))

This should accomplish what you're looking for.

zukand

Hey Guys,

Trying to appropriate this for my uses..

I've got a bunch of nested jobs. They'll always have a different number of children.

Each child row has a status. Red, Green or Blue.

I'd like the parent to be red to represent when the children are all, or a majority red. (percentage would be great!)

I'd like the parent to go blue when there is less than 2 red balls left (or alternatiley if this isn't possible, when a majority is either green or blue)

I'd like the parent to go green only when ALL the children are green.

I'll keep playing for now, but any help would be greatly appreciated

zukand

So.. I've been playing.. and I've got it down to

=IF(COUNTIF(CHILDREN(), "Red") > (COUNT(CHILDREN()) / 2), "Red", IF(AND(COUNTIF(CHILDREN(), "Green") > COUNTIF(CHILDREN(), "Red"), COUNTIF(CHILDREN(), "Green") > COUNTIF(CHILDREN(), "Red")), "Blue"))

But I haven't worked out getting it to go green once they are all green.

I've really struggled to find good documentation on the AND() function (no idea what it does!) and a few of the others.. only have a very basic understanding

zukand

not letting me edit -.-

I've got it to here.

=IF(COUNTIF(CHILDREN(), "Red") > (COUNT(CHILDREN()) / 2), "Red", IF(AND(COUNTIF(CHILDREN(), "Green") > COUNTIF(CHILDREN(), "Red"), COUNTIF(CHILDREN(), "Green") > COUNTIF(CHILDREN(), "Red")), "Blue", "Blue"))

I feel like I just need to be able to add

IF(COUNTIF(CHILDREN(), "Green") = (COUNT(CHILDREN()), "Green"

but can't seem to get it working

John Hammond

zukand, try this:

=IF(COUNTIF(CHILDREN(), "Green") = COUNT(CHILDREN()), "Green", IF(COUNTIF(CHILDREN(), "Red") > (COUNT(CHILDREN()) / 2), "Red", IF(AND(COUNTIF(CHILDREN(), "Green") > COUNTIF(CHILDREN(), "Red"), COUNTIF(CHILDREN(), "Green") > COUNTIF(CHILDREN(), "Red")), "Blue", "Blue")))

zukand

BOOM! Worked like a charm Thanks for that

Ilana Preston

I'm trying to build on the concepts here since I think this is a great start but I'm having a bit of trouble. Basically what I want my formula to do is:

If there are any Red balls, make the parent ball Red
If there are no Red balls but there are Yellow balls, make the parent ball Yellow
If there are no Red balls and no Yellow balls, make the parent ball Green

Essentially, I want the parent row to reflect the "worst" status of the child rows, regardless as to whether or not there are also good statuses in the child rows.

Pranav

Hello,

I need help to build the formula as follow:

When I change Column B to (InProgress, Complete, or Risk) My Column A will change to (Red, Yellow, or Green).

Also

If there are any Red in the children, I want to show Red in the parent,

If there are no Red but there are Yellow in the children, Show Yellow in the parent.

If there are no Red or Yellow but there is Green in the children, Show Green in the parent.

Following drop down list are under Column B.

In Progress = Yellow

Complete = Green

Risk = Red

Thanks

Patricia Thurston

I'm trying to figure out the same formula as llanna above. If any of the children are red, I want the status change to red. If the children are yellow, status should change to yellow, etc. I'm using it for milestones, so there should never (or rarely be) a milestone that is red, and another that is yellow. It will either be late (red or yellow), green (on track) or grey (completed).

Thanks for your help

Josh Garcia

@ Patricia Thurston

I had the same need, and came up with this formula:

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

The presence of any Red makes the parent red, Yellow for Yellow, Green for Green, Blue for Blue, and if none are present, it's blank.

h1pst3r

This thread is great -- thank you for the headstart on this formula. For my purposes, my child rows are using a dropdown menu called "Confidence" with percentage (10%, 20%...100%).

I'd like to set the the ball color for the parent row to be based on the highest percentage of any of the children.

If any child row is set to 90 or 100% the parent should be blue.

If any child row is set to 70 or 80% the parent should be green.

If any child row is set to 50 or 60% the parent should be yellow.

If any parent row is set from 10 to 40% the parent should be red.

Thanks in advance for any assistance!

-Chris

Joel McClurg

@ Josh Garcia

Your reply from March 8, 2017, worked great. Thank you.