Formula Challenge

05/29/21
Accepted

Hi Smarties,

I am trying to get this formula right in the Formula Challenge from Smartsheet. I am not sure what I did wrong so hoping fellow Smarties could help!


In the Parent Row symbols column, write a formula that returns the most common value of its children.

If there are equal or more reds than yellows or greens, the parent status should be red.

If there are equal or more yellows than reds or greens, the parent status should be yellow.

If there are equal or more greens than reds or yellows, the parent status should be green.

Otherwise, the parent status should be blue.

----------------

I have started with the formula below and tried every different criterions, still not successful. I just need to get Green in the "If there are equal or more reds than yellows or greens, the parent status should be red."

I thought this formula would have worked, but when I changed all Children rows to Yellow, it shows Red, when it is supposed to show "Blank" if using the formula below?

=IF(COUNTIFS(COUNT(CHILDREN()), "Red") >= COUNT(CHILDREN()), "Yellow", "Red")




Tags:

Best Answer

  • Mark CronkMark Cronk ✭✭✭✭✭
    edited 05/31/21 Accepted Answer

    So close. We failed to account for the absence of a red, green or yellow. Good catch!.

    Try:

    =IF(AND(COUNTIF(CHILDREN(), "Red") >= COUNTIF(CHILDREN(), "Yellow"), COUNTIF(CHILDREN(), "Red") >= COUNTIF(CHILDREN(), "Green"), COUNTIF(Children(), "Red")>0)), "Red", IF(AND(COUNTIF(CHILDREN(), "Yellow") >= COUNTIF(CHILDREN(), "Red"), COUNTIF(CHILDREN(), "Yellow") >= COUNTIF(CHILDREN(), "Green"), COUNTIF(children(), "Yellow")>0)), "Yellow", IF(AND(COUNTIF(CHILDREN(), "Green")= > COUNTIF(CHILDREN(), "Red"), COUNTIF(CHILDREN(), "Green") =>COUNTIF(CHILDREN(), "Yellow"), COUNTIF(Children(),"green")>0)), "Green", "Blue")))

    The >= are the same. IF statements work in order and stops when true. The formula is set up so that if 2 are equal the lower status wins. If Red=Yellow or Red=Green it will be Red. If there is 1 Red and the rest are Blue, it will be Red. It then repeats for Yellow and then Green.

    Work now?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Answers

  • Mark CronkMark Cronk ✭✭✭✭✭

    Try:

    =IF(AND(COUNT(CHILDREN(), "Red") >= COUNT(CHILDREN(), "Yellow"), COUNT(CHILDREN(), "Red") >= COUNT(CHILDREN(), "Green")), "Red", IF(AND(COUNT(CHILDREN(), "Yellow") >= COUNT(CHILDREN(), "Red"), COUNT(CHILDREN(), "Yellow") >= COUNT(CHILDREN(), "Green")), "Yellow", "Green"))

    Work?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Syed MuhafzalSyed Muhafzal ✭✭✭✭

    Hi Mark,

    No, it doesnt work. From the screenshot below, it is supposed to be showing Green on the parent level but it is still showing Red? I have also changed this to all Yellow, and it is still showing Red at the Parent level.












    I have added the Green formula in the formula you have provided above with Blue in the end.

    =IF(AND(COUNT(CHILDREN(), "Red") >= COUNT(CHILDREN(), "Yellow"), COUNT(CHILDREN(), "Red") >= COUNT(CHILDREN(), "Green")), "Red", IF(AND(COUNT(CHILDREN(), "Yellow") >= COUNT(CHILDREN(), "Red"), COUNT(CHILDREN(), "Yellow") >= COUNT(CHILDREN(), "Green")), "Yellow", IF(AND(COUNT(CHILDREN(), "Green") >= COUNT(CHILDREN(), "Red"), COUNT(CHILDREN(), "Green") >= COUNT(CHILDREN(), "Yellow")), "Green", "Blue")))


    Thanks

    Syed

  • Mark CronkMark Cronk ✭✭✭✭✭

    Hi,

    The funtion needs to be COUNTIF. Try:

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

    Work now?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Syed MuhafzalSyed Muhafzal ✭✭✭✭

    Thanks Mark.

    Almost there.

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

    However the above is giving me Red and when all children are Blue

    Expected Result - blue

    On the same note, I noticed the argument given does not make sense either.

    If Yellow equals Green, it should be Yellow

    If Green equals Yellow, it should be Green

    Sounds like the same thing to me?

    Thanks

  • Mark CronkMark Cronk ✭✭✭✭✭
    edited 05/31/21 Accepted Answer

    So close. We failed to account for the absence of a red, green or yellow. Good catch!.

    Try:

    =IF(AND(COUNTIF(CHILDREN(), "Red") >= COUNTIF(CHILDREN(), "Yellow"), COUNTIF(CHILDREN(), "Red") >= COUNTIF(CHILDREN(), "Green"), COUNTIF(Children(), "Red")>0)), "Red", IF(AND(COUNTIF(CHILDREN(), "Yellow") >= COUNTIF(CHILDREN(), "Red"), COUNTIF(CHILDREN(), "Yellow") >= COUNTIF(CHILDREN(), "Green"), COUNTIF(children(), "Yellow")>0)), "Yellow", IF(AND(COUNTIF(CHILDREN(), "Green")= > COUNTIF(CHILDREN(), "Red"), COUNTIF(CHILDREN(), "Green") =>COUNTIF(CHILDREN(), "Yellow"), COUNTIF(Children(),"green")>0)), "Green", "Blue")))

    The >= are the same. IF statements work in order and stops when true. The formula is set up so that if 2 are equal the lower status wins. If Red=Yellow or Red=Green it will be Red. If there is 1 Red and the rest are Blue, it will be Red. It then repeats for Yellow and then Green.

    Work now?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Syed MuhafzalSyed Muhafzal ✭✭✭✭

    Hi Mark,

    Amazing. I just had to remove a few close parentheses (after">0") from the formula and it is working fine. You did all the work though - so thank you!

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

    Thanks☺️

    Syed

  • Mark CronkMark Cronk ✭✭✭✭✭

    Finally. Thanks for bringing a good challenge to the Community. Glad you found your solution. Well done.

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Sign In or Register to comment.