Formula Challenge

Options
Syed Muhafzal
Syed Muhafzal ✭✭✭✭✭
in Formulas and Functions

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



image.png


Tags:
· Share on FacebookShare on Twitter

Best Answer

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    edited 05/31/21 Answer ✓
    Options

    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.

    · Share on FacebookShare on Twitter

Answers

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Options

    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.

    · Share on FacebookShare on Twitter
  • Syed Muhafzal
    Syed Muhafzal ✭✭✭✭✭
    Options

    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.

    image.png












    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

    · Share on FacebookShare on Twitter
  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Options

    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.

    · Share on FacebookShare on Twitter
  • Syed Muhafzal
    Syed Muhafzal ✭✭✭✭✭
    Options

    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

    image.png

    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

    · Share on FacebookShare on Twitter
  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    edited 05/31/21 Answer ✓
    Options

    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.

    · Share on FacebookShare on Twitter
  • Syed Muhafzal
    Syed Muhafzal ✭✭✭✭✭
    Options

    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

    · Share on FacebookShare on Twitter
  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Options

    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.

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!