IF, AND, CHILDREN formula
I have a project checklist that has parent rows where I am trying to show an overall status trend for the children rows that fall under each parent. I have a current formula set up with the use of a helper column that says: =IF(Scoring@row = 0, "Sideways", IF(Scoring@row < 2, "Down", IF(Scoring@row < 3, "Angle Down", IF(Scoring@row < 4, "Angle Up", IF(Scoring@row = 4, "Up", "")))))
This was ok until I realized that I have some parents that have children rows that show 4's for complete and 0's for not started, so the parent trend is showing as a 4  but the parent is NOT actually complete, and I do not want to include the 0's on purpose so that it does not show a downward trend for the parent. *Man, I hope that makes sense!*
Here is what I have tried, but it keeps giving me an error: =IF(Scoring@row = 0, "Sideways", IF(Scoring@row < 2, "Down", IF(Scoring@row < 3, "Angle Down", IF(Scoring@row < 4, "Angle Up", IF(AND(Scoring@row = 4, NOT(CONTAINS("0", CHILDREN(Scoring@row)), "Up", "Angle Up")))))))
Basically, I need the formula to say:
If the Scoring Row = 0, then show the Sideways Arrow;
If the Scoring Row <2, then show the Down Arrow;
If the Scoring Row is less than 3, then show the Angle Down Arrow;
If the Scoring Row is less than 4, then show the Angle Up Arrow;
If the Scoring Row is equal to 4 AND the children rows of the Scoring Row do not contain 0, then show the Up Arrow; but if the Scoring Row is equal to 4 and the children rows of the Scoring Row DO contain 0, then show the Angle Up Arrow. Help?!
Best Answer

I actually finally got a solution over the weekend, but thank you! This was what wound up working (I haven't tried your suggestion though, to be fair): =IF(AND(NOT(HAS(CHILDREN(Scoring@row), 0)), Scoring@row = 4), "Up", IF(AND(HAS(CHILDREN(Scoring@row), 0), Scoring@row = 4), "Angle Up", IF(Scoring@row = 0, "Sideways", IF(Scoring@row < 2, "Down", IF(Scoring@row < 3, "Angle Down", IF(Scoring@row < 4, "Angle Up"))))))
Answers

Give this a try:
=IF(Scoring@row = 4, IF(COUNTIFS(CHILDREN(Scoring@row), @cell = 0) = 0, "Up", "Angle Up"), IF(Scoring@row = 3, "Angle Up", IF(Scoring@row = 2, "Angle Down", IF(Scoring@row = 1, "Down", "Sideways"))))

I actually finally got a solution over the weekend, but thank you! This was what wound up working (I haven't tried your suggestion though, to be fair): =IF(AND(NOT(HAS(CHILDREN(Scoring@row), 0)), Scoring@row = 4), "Up", IF(AND(HAS(CHILDREN(Scoring@row), 0), Scoring@row = 4), "Angle Up", IF(Scoring@row = 0, "Sideways", IF(Scoring@row < 2, "Down", IF(Scoring@row < 3, "Angle Down", IF(Scoring@row < 4, "Angle Up"))))))

Glad you were able to get it sorted!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.6K Get Help
 403 Global Discussions
 215 Industry Talk
 455 Announcements
 4.7K Ideas & Feature Requests
 141 Brandfolder
 136 Just for fun
 56 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 296 Events
 36 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!