Average Children Rows with RYG

Laurie Bearden

Hi all. I'm attempting to calculate an average for RYG balls in the parent row. I have gotten to the point of calculating all of my colors, but I'm not sure how to get the average. Here is my calculation thus far:

=AVG(COUNTIF(CHILDREN(), "Green") + COUNTIF(CHILDREN(), "Yellow") * 2 + COUNTIF(CHILDREN(), "Red") * 3)

This comes up with a count, but the count ends up the same whether I use the AVG or not (57).

I'd like my end result to be a number between 1 and 3.

Jamison

It is returning 57 because the only number being passed to the AVG function is 57. So, you are asking it, what is the average of 57, not what is the average of 2, 3, 1, 3, 2 and so on.

So, using what you have, just divide it by thetotal number of children and remove the AVG from the beginning:

=((COUNTIF(CHILDREN(), "Green") + COUNTIF(CHILDREN(), "Yellow") * 2 + COUNTIF(CHILDREN(), "Red") * 3)) / COUNT(CHILDREN())

There might be an easier way, but this will work.

Laurie Bearden

I was able to make it work with the help from a colleague. It looks a lot different from yours lol.

=IF(AND(Phase7 = "Stalled", OR([On Time Status]7 = "Early", [On Time Status]7 = "On Time")), "Yellow", IF(AND([On Time Status]7 = "Late", OR(Phase7 = "Not Started", Phase7 = "In Progress", Phase7 = "Stalled", Phase7 = "Unknown")), "Red", "Green"))

This was able to give us the desired result of the average color ball. Thank you so much for getting back to me.I know your formula will help me and others in the future, as well.

Laurie Bearden

Sorry, that was a formula for a different sheet.

The formula for the average RYG is:

=IF((COUNTIF(CHILDREN(), "Green") + COUNTIF(CHILDREN(), "Yellow") * 2 + COUNTIF(CHILDREN(), "Red") * 3) / COUNT(CHILDREN()) < 2, "Green", IF((COUNTIF(CHILDREN(), "Green") + COUNTIF(CHILDREN(), "Yellow") * 2 + COUNTIF(CHILDREN(), "Red") * 3) / COUNT(CHILDREN()) < 3, "Red", "Yellow"))