Formula Challenge
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")
Best 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
-
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.
-
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
-
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.
-
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
-
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.
-
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
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 466 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!