Average of RGY Children

Madeline Brannen

Hello,

I currently have a long long formula to look at the RGY balls of the children of a task and create a color based off of the sum of yellows vs. red vs. green and which one is greater than the other (see below).

 

=IF(AND(COUNTIF(CHILDREN(), "") > COUNTIF(CHILDREN(), "Yellow"), COUNTIF(CHILDREN(), "") > COUNTIF(CHILDREN(), "Red"), COUNTIF(CHILDREN(), "") > COUNTIF(CHILDREN(), "Green")), "", 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", IF(AND(COUNTIF(CHILDREN(), "Yellow") > COUNTIF(CHILDREN(), "Green"), COUNTIF(CHILDREN(), "Yellow") > COUNTIF(CHILDREN(), "Red")), "Yellow", IF(AND(COUNTIF(CHILDREN(), "Yellow") = COUNTIF(CHILDREN(), "Red")), "Yellow", IF(AND(COUNTIF(CHILDREN(), "Red") = COUNTIF(CHILDREN(), "Green")), "Yellow", IF(AND(COUNTIF(CHILDREN(), "Yellow") = COUNTIF(CHILDREN(), "Red") = COUNTIF(CHILDREN(), "Green")), "Yellow", "Green")))))))

 

While this formula works, I was wondering if there was a way to average the RGY balls of all the children to make the formula a little simpler...?

 

Thank you for your help!

 

Greg Gates

I don't think there's necessarily a way to make your formula itself any simpler (someone out there definitely correct me if I'm wrong!). If you were really desperate to make it a little less obtuse, my only recommendation would be to break some of those steps out into separate cells. For example, you could count the number of red/yellow/green/blank statuses and store them in hidden columns at the end of your sheet so you don't have to keep using "COUNTIF." (especially on things you've already counted). If adding new columns isn't an option, I think you've already found your best solution!

J. Craig Williams

Here's a smaller one:

 

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

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

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

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

"unresolved"))))

 

I also believe your last IF statement is never hit.

I'm not sure that it gives the right answer, though it might not throw an error.

 

This formula gives me the wrong result:

 

=IF([Column5]2 = [Column5]3 = [Column5]4, "all equal", "not all equal")

 

I also don't think I agree that 4 blanks, 3 yellows, 3 reds, and 3 greens should show blank.

 

I would probably use Greg's suggestion to store the COUNTIF values and average them.

 

The last "IF" in my equation can go away after testing - I could not get to 'unresolved' which means that the last condition is "yellow", but testing was limited.

 

Craig