Formula Challenge

Syed Muhafzal

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

Mark Cronk

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

Mark 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

Syed 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 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

Syed 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 Cronk

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

Syed 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 Cronk

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

Mark