Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
Average of RGY Children
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!
Comments
-
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!
-
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
