IF - THEN - IF/AND Formulation

Guaca Mohle

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.

David Joyeuse

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!

Guaca Mohle

This worked like a charm! THANK YOU!

Guaca Mohle

Now I'm graduating to the next level of complexity for this formula build-out.

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", "")))) 

Genevieve P.

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"))

Guaca Mohle

Thanks awesome. Thank you so much @Genevieve P !