RYG Ball count if formula issue.

J.Barrow

Ok I am trying to get an formula for over all health of a project by using countifs formula for the sub status balls. this is the formula I am using.

=IF(COUNTIFS(CHILDREN(), "Green") >= (COUNTIFS(CHILDREN(), "Red") + COUNTIFS(CHILDREN(), "Yellow")), "Green", IF(OR(COUNTIFS(CHILDREN(), "Yellow") >= (COUNTIFS(CHILDREN(), "Green") + COUNTIFS(CHILDREN(), "Red")), COUNTIFS(CHILDREN(), "Red") = COUNTIFS(CHILDREN(), "Green"), COUNTIFS(CHILDREN(), "Red") = COUNTIFS(CHILDREN(), "Yellow"), COUNTIFS(CHILDREN(), "Green") = COUNTIFS(CHILDREN(), "Yellow")), "Yellow", IF(OR(COUNTIFS(CHILDREN(), "Red") >= (COUNTIFS(CHILDREN(), "Green") + COUNTIFS(CHILDREN(), "Yellow")), (COUNTIFS(CHILDREN(), "Red") + COUNTIFS(CHILDREN(), "Yellow")) >= COUNTIFS(CHILDREN(), "Green")), "Red", "Green")))

The issue is that I have all reds and it is still showing yellow in the cell vs. red.

If I flip the formula to put red before yellow like this:

=IF(COUNTIFS(CHILDREN(), "Green") >= (COUNTIFS(CHILDREN(), "Red") + COUNTIFS(CHILDREN(), "Yellow")), "Green", IF(OR(COUNTIFS(CHILDREN(), "Red") >= (COUNTIFS(CHILDREN(), "Green") + COUNTIFS(CHILDREN(), "Yellow")), (COUNTIFS(CHILDREN(), "Red") + COUNTIFS(CHILDREN(), "Yellow")) >= COUNTIFS(CHILDREN(), "Green")), "Red", IF(OR(COUNTIFS(CHILDREN(), "Yellow") >= (COUNTIFS(CHILDREN(), "Green") + COUNTIFS(CHILDREN(), "Red")), COUNTIFS(CHILDREN(), "Red") = COUNTIFS(CHILDREN(), "Green"), COUNTIFS(CHILDREN(), "Red") = COUNTIFS(CHILDREN(), "Yellow"), COUNTIFS(CHILDREN(), "Green") = COUNTIFS(CHILDREN(), "Yellow")), "Yellow", "Green")))

Then if I have all yellow they turn red. :(

I just need away that if it calculates properly no matter the "or" order.

J.Barrow

@Paul Newcome

I realized that we needed to multiply the child count by 2 to get the percentages to work.

so I changed the formula to:

=IF(((COUNTIFS(CHILDREN(), "Green") * 2) + COUNTIFS(CHILDREN(), "Yellow")) / (COUNT(CHILDREN()) * 2) <= 0.49, "Red", IF(((COUNTIFS(CHILDREN(), "Green") * 2) + COUNTIFS(CHILDREN(), "Yellow")) / (COUNT(CHILDREN()) * 2) <= 0.74, "Yellow", "Green"))

Thanks for all the help and I hope that helps someone else.

Paul Newcome

Your first formula outputs yellow because of the bit in bold:

=IF(COUNTIFS(CHILDREN(), "Green") >= (COUNTIFS(CHILDREN(), "Red") + COUNTIFS(CHILDREN(), "Yellow")), "Green", IF(OR(COUNTIFS(CHILDREN(), "Yellow") >= (COUNTIFS(CHILDREN(), "Green") + COUNTIFS(CHILDREN(), "Red")), COUNTIFS(CHILDREN(), "Red") = COUNTIFS(CHILDREN(), "Green"), COUNTIFS(CHILDREN(), "Red") = COUNTIFS(CHILDREN(), "Yellow"), COUNTIFS(CHILDREN(), "Green") = COUNTIFS(CHILDREN(), "Yellow")), "Yellow", IF(OR(COUNTIFS(CHILDREN(), "Red") >= (COUNTIFS(CHILDREN(), "Green") + COUNTIFS(CHILDREN(), "Yellow")), (COUNTIFS(CHILDREN(), "Red") + COUNTIFS(CHILDREN(), "Yellow")) >= COUNTIFS(CHILDREN(), "Green")), "Red", "Green")))

Since the count of greens is zero and the count of yellows is zero, they are equal which is triggering a true argument for your "Yellow" output.

The second formula's output issue stems from this section in bold:

=IF(COUNTIFS(CHILDREN(), "Green") >= (COUNTIFS(CHILDREN(), "Red") + COUNTIFS(CHILDREN(), "Yellow")), "Green", IF(OR(COUNTIFS(CHILDREN(), "Red") >= (COUNTIFS(CHILDREN(), "Green") + COUNTIFS(CHILDREN(), "Yellow")), (COUNTIFS(CHILDREN(), "Red") + COUNTIFS(CHILDREN(), "Yellow")) >= COUNTIFS(CHILDREN(), "Green")), "Red", IF(OR(COUNTIFS(CHILDREN(), "Yellow") >= (COUNTIFS(CHILDREN(), "Green") + COUNTIFS(CHILDREN(), "Red")), COUNTIFS(CHILDREN(), "Red") = COUNTIFS(CHILDREN(), "Green"), COUNTIFS(CHILDREN(), "Red") = COUNTIFS(CHILDREN(), "Yellow"), COUNTIFS(CHILDREN(), "Green") = COUNTIFS(CHILDREN(), "Yellow")), "Yellow", "Green")))

In that screenshot, "Red" (0) + "Yellow" (1) is greater than "Green" (0) which means you are triggering a true argument for your "Red" output.

Are you able to type out the logic in plain sentences and in order of importance similar to:

If the majority are red, red

If the majority are yellow, yellow

If the majority are green, green

or

If red is the highest individual count, red

If yellow is the highest individual count, yellow

If green is the highest individual count, green

or

Whatever it is you are wanting to accomplish with the highest priority being first.

J.Barrow

@Paul Newcome

I will try as I am looking to try and develop a over all guidance of project health.

I am struggling with your request as I can come up with different scenario's for everything.

if I was to do it as a math problem I might do it something like this.

There are 10 tasks in a sub group of tasks: each task is work 2 points so there are a total of 20 points.

Green=2 points

Yellow = 1 points

Red = 0 points

So you would then add up the points and divide by the total points available and it would then the following would be true.

Green = 0.75-1

Yellow = 0.50-0.74

Red = 0-0.49

Paul Newcome

@J.Barrow We should be able to work with that.

Green Score:

=COUNTIFS(CHILDREN(), "Green") * 2

Yellow Score:

=COUNTIFS(CHILDREN(), "Yellow")

Red score:

No formula as anything times zero is going to be zero.

Add Green and Yellow together:

=(COUNTIFS(CHILDREN(), "Green") * 2) + COUNTIFS(CHILDREN(), "Yellow")

Divide by total number of children to get the overall score:

=((COUNTIFS(CHILDREN(), "Green") * 2) + COUNTIFS(CHILDREN(), "Yellow")) / COUNT(CHILDREN())

Drop that into a nested IF:

=IF(final_score <= 0.49, "Red", IF(final_score <= 0.74, "Yellow", "Green"))

=IF(((COUNTIFS(CHILDREN(), "Green") * 2) + COUNTIFS(CHILDREN(), "Yellow")) / COUNT(CHILDREN()) <= 0.49, "Red", IF(((COUNTIFS(CHILDREN(), "Green") * 2) + COUNTIFS(CHILDREN(), "Yellow")) / COUNT(CHILDREN()) <= 0.74, "Yellow", "Green"))

J.Barrow

@Paul Newcome,

That did not work as I cut and pasted in your formula into my test sheet to see if it would work and it only shows green always no matter the color combination that should clearly be something other than green.

Formula is the following:

=IF(((COUNTIFS(CHILDREN(), "Green") * 2) + COUNTIFS(CHILDREN(), "Yellow")) / COUNT(CHILDREN()) <= 0.49, "Red", IF(((COUNTIFS(CHILDREN(), "Green") * 2) + COUNTIFS(CHILDREN(), "Yellow")) / COUNT(CHILDREN()) <= 0.74, "Yellow", "Green"))

J.Barrow

@Paul Newcome

I realized that we needed to multiply the child count by 2 to get the percentages to work.

so I changed the formula to:

=IF(((COUNTIFS(CHILDREN(), "Green") * 2) + COUNTIFS(CHILDREN(), "Yellow")) / (COUNT(CHILDREN()) * 2) <= 0.49, "Red", IF(((COUNTIFS(CHILDREN(), "Green") * 2) + COUNTIFS(CHILDREN(), "Yellow")) / (COUNT(CHILDREN()) * 2) <= 0.74, "Yellow", "Green"))

Thanks for all the help and I hope that helps someone else.