IF  THEN  IF/AND Formulation
Having a hard time with formula logic. The setup for a RYGB column:
 If the row is a parent row, I want it to do (X), where is as an elaborate string of calculations based on a another column's values
 If it isn't a parent row: AND IF it is complete, then I want a BLUE Harvey ball, else, a GREEN Harvey ball
Can this be done? I've tried the following and getting an #INVALID DATA TYPE error:
=IF(COUNT(CHILDREN()) > 0, "Yellow", AND(IF([% Complete]@row = 1, "Blue", "Green")))
Note: "Yellow" in the above is just a placeholder until I can get this much working. Later, I'll fold in the calculations for what to do when it is a Parent row.
Answers

Hi @Guaca Mohle
Your formula should be like this:
=IF(COUNT(CHILDREN())>0, "Yellow", IF([% Complete]@row=1, "Blue", "Green"))
No need for the AND function in your case. Not sure where the #INVALID DATA TYPE comes from, maybe check if the column is locked for RYGB balls, you may want to unlock it then if you want to display something else than balls here.
Hope it helped!

This worked like a charm! THANK YOU!

Now I'm graduating to the next level of complexity for this formula buildout.
In another thread, I came across a formula i would like to leverage inside of the formula above (to replace the "YELLOW") in the logic above.
Essentially: if the row has children, I want it to change the ball color based on the children's ball health:
Color the parent row RED if: There are more than 3 yellows or 2 reds among the children
Color the parent row YELLOW if: There are fewer than 3 yellows or 2 reds among the children.
Color the parent row BLUE if all children are blue,
Else Green.
But if the row is NOT a parent row, and %Complete is = 1, then BLUE, else GREEN.
The sample formula I came across is as follows, but toward the latter half, I get lost in the logic:
=IF(OR(COUNTIF(CHILDREN(), "Yellow") > 3, COUNTIF(CHILDREN(), "Red") > 2), "Red", IF(OR(COUNTIF(CHILDREN(), "Yellow") > 0, COUNTIF(CHILDREN(), "Red") > 0), "Yellow", IF(COUNTIF(CHILDREN(), "Blue") = (COUNT(CHILDREN()) + COUNTIF(CHILDREN(), ISBLANK(@cell))), "Blue", IF(OR(COUNTIF(CHILDREN(), "Green") > 0, AND(COUNTIF(CHILDREN(), "Blue") < (COUNT(CHILDREN()) + COUNTIF(CHILDREN(), ISBLANK(@cell))), COUNTIF(CHILDREN(), "Blue") > 0)), "Green", ""))))

Hi @Guaca Mohle
I'm happy to help break down this formula for you:
RED
If the Children have more than 3 yellow OR more than 2 Red, return Red
=IF(OR(COUNTIF(CHILDREN(), "Yellow") > 3, COUNTIF(CHILDREN(), "Red") > 2), "Red",
YELLOW
Otherwise, if the Children have any yellow OR any red, return "Yellow"
IF(OR(COUNTIF(CHILDREN(), "Yellow") > 0, COUNTIF(CHILDREN(), "Red") > 0), "Yellow",
BLUE
Otherwise, if the Children's number of blue status balls is the same as the number of child rows (blank or not blank), then return "Blue"
IF(COUNTIF(CHILDREN(), "Blue") = (COUNT(CHILDREN()) + COUNTIF(CHILDREN(), ISBLANK(@cell))), "Blue",
GREEN
Otherwise, if there are any number of green status balls, AND the count of blue status balls is less than the number of child rows (all child rows, blank or not blank), OR if there are any blue balls, then return "Green"
IF(OR(COUNTIF(CHILDREN(), "Green") > 0, AND(COUNTIF(CHILDREN(), "Blue") < (COUNT(CHILDREN()) + COUNTIF(CHILDREN(), ISBLANK(@cell))),
COUNTIF(CHILDREN(), "Blue") > 0)), "Green", ""))))
Otherwise, blank.
Your Formula
To add this statement to your formula, it would be pasted in exactly where you have "yellow", like so:
=IF(COUNT(CHILDREN())>0, IF(OR(COUNTIF(CHILDREN(), "Yellow") > 3, COUNTIF(CHILDREN(), "Red") > 2), "Red", IF(OR(COUNTIF(CHILDREN(), "Yellow") > 0, COUNTIF(CHILDREN(), "Red") > 0), "Yellow", IF(COUNTIF(CHILDREN(), "Blue") = (COUNT(CHILDREN()) + COUNTIF(CHILDREN(), ISBLANK(@cell))), "Blue", IF(OR(COUNTIF(CHILDREN(), "Green") > 0, AND(COUNTIF(CHILDREN(), "Blue") < (COUNT(CHILDREN()) + COUNTIF(CHILDREN(), ISBLANK(@cell))), COUNTIF(CHILDREN(), "Blue") > 0)), "Green")))), IF([% Complete]@row=1, "Blue", "Green"))

Thanks awesome. Thank you so much @Genevieve P !
Help Article Resources
Categories
Check out the Formula Handbook template!