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

Screenshot 2024-05-13 at 7.55.46 AM.png

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

Find more posts tagged with

Accepted answers

KPH

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

All comments

KPH

Something like this:

Robin H 77

That's a great idea, thanks!

Robin H 77

Your suggestion helped me find the problem and fix it, thanks again!

KPH

Wonderful! Glad I could point you in the right direction.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.