Any ideas why a formula wouldn't work on all rows?
I have a formula that is working on at least one row, but not all rows, and I have the necessary data in all rows for it to work, so I am boggled as to why it isn't working on all rows
:
=IF([#Children]@row > 0, IF(COUNTIF(CHILDREN(), "MISSING DATES") > 0, "MISSING DATES", IF(COUNTIF(CHILDREN(), "Red") > 0, "Red", IF(COUNTIF(CHILDREN(), "Yellow") > 0, "Yellow", IF(COUNTIF(CHILDREN(), "Green") > 0, "Green", "Blue")))), IF(ISBLANK([Task Name]@row), "", IF([# Workdays]@row = "MISSING DATES", "MISSING DATES", IF(OR(Status@row = "Complete", Status@row = "Cancelled", Status@row = "Deferred", Status@row = "On Hold"), "Blue", IF(OR([Days Til Due]@row = "Past Due", Status@row = "In Jeopardy"), "Red", IF(OR(AND(Status@row = "Not Started", Start@row < TODAY()), Status@row = "Challenged"), "Yellow", "Green"))))))
Best Answer
-
As it is a nested IF formula, it is probably working for rows that evaluate to true in one of the earlier IFs, then failing when it reaches the part of the formula that has a problem in it. I would change all the outcomes to unique words and test it. See which words appear and which do not. The problem will be after the part of the formula that returns the last word. You can then focus on troubleshooting that part.
Something like this:
=IF([#Children]@row > 0, IF(COUNTIF(CHILDREN(), "MISSING DATES") > 0, "MISSING DATES", IF(COUNTIF(CHILDREN(), "Red") > 0, "cow", IF(COUNTIF(CHILDREN(), "Yellow") > 0, "pig", IF(COUNTIF(CHILDREN(), "Green") > 0, "Emu", "Goat")))), IF(ISBLANK([Task Name]@row), "", IF([# Workdays]@row = "MISSING DATES", "MISSING DATES", IF(OR(Status@row = "Complete", Status@row = "Cancelled", Status@row = "Deferred", Status@row = "On Hold"), "Rabbit", IF(OR([Days Til Due]@row = "Past Due", Status@row = "In Jeopardy"), "Chick", IF(OR(AND(Status@row = "Not Started", Start@row < TODAY()), Status@row = "Challenged"), "Hedgehog", "Mink"))))))
Answers
-
As it is a nested IF formula, it is probably working for rows that evaluate to true in one of the earlier IFs, then failing when it reaches the part of the formula that has a problem in it. I would change all the outcomes to unique words and test it. See which words appear and which do not. The problem will be after the part of the formula that returns the last word. You can then focus on troubleshooting that part.
Something like this:
=IF([#Children]@row > 0, IF(COUNTIF(CHILDREN(), "MISSING DATES") > 0, "MISSING DATES", IF(COUNTIF(CHILDREN(), "Red") > 0, "cow", IF(COUNTIF(CHILDREN(), "Yellow") > 0, "pig", IF(COUNTIF(CHILDREN(), "Green") > 0, "Emu", "Goat")))), IF(ISBLANK([Task Name]@row), "", IF([# Workdays]@row = "MISSING DATES", "MISSING DATES", IF(OR(Status@row = "Complete", Status@row = "Cancelled", Status@row = "Deferred", Status@row = "On Hold"), "Rabbit", IF(OR([Days Til Due]@row = "Past Due", Status@row = "In Jeopardy"), "Chick", IF(OR(AND(Status@row = "Not Started", Start@row < TODAY()), Status@row = "Challenged"), "Hedgehog", "Mink"))))))
-
That's a great idea, thanks!
-
Your suggestion helped me find the problem and fix it, thanks again!
-
Wonderful! Glad I could point you in the right direction.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!