IF, AND, CHILDREN formula

jeannaplummer

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?!

jeannaplummer

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

Paul Newcome

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

jeannaplummer

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

Paul Newcome

Glad you were able to get it sorted!