IF - THEN - IF/AND Formulation

Options

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.

Tags:

Answers

  • David Joyeuse
    David Joyeuse ✭✭✭✭✭
    Options

    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
    Guaca Mohle ✭✭✭✭
    Options

    This worked like a charm! THANK YOU!

  • Guaca Mohle
    Guaca Mohle ✭✭✭✭
    Options

    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.
    Genevieve P. Employee Admin
    Options

    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
    Guaca Mohle ✭✭✭✭
    Options

    Thanks awesome. Thank you so much @Genevieve P !

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!