Only Part of my formula is not working and I can't figure out why

I created the formula below and it is working for the section that says "Validations - Issue Validations" and "Validations - Regulatory Issue" part and I can't figure out why

=IF([Risk Activity]@row = "Control Testing", IF([Actual Cycle Time]@row <= 75, "Green", IF([Actual Cycle Time]@row <= 90, "Yellow", "Red")), IF([Risk Activity]@row = "Compliance thematic review", IF([Actual Cycle Time]@row <= 75, "Green", IF([Actual Cycle Time]@row <= 90, "Yellow", "Red")), IF([Risk Activity]@row = "Validations – Issue Validations ", IF([Actual Cycle Time]@row < =22, "Green", IF([Actual Cycle Time]@row <= 30, "Yellow", "Red")), ")), IF([Risk Activity]@row = "Validations – Regulatory Issue ", IF([Actual Cycle Time]@row < =22, "Green", IF([Actual Cycle Time]@row <= 30, "Yellow", "Red")), ""))))

Find more posts tagged with

Sheets

Formulas

Community Platform

Conditional Formatting

Comments and Conversations

Accepted answers

Paul Newcome

Ok. Give this a try:

=IF(OR([Risk Activity]@row = "Validations - Issue Validations", [Risk Activity]@row = "Validations - Regulatory Issue"), IF([Actual Cycle Time]@row >= 32, "Red", IF([Actual Cycle Time]@row >= 23, "Yellow", "Green")), IF(OR([Risk Activity]@row = "Compliance Thematic Review", [Risk Activity]@row = "Control Testing"), IF([Actual Cycle Time]@row >= 91, "Red", IF([Actual Cycle Time]@row >= 76, "Yellow", "Green"))))

All comments

Paul Newcome

Can you show a screenshot of some rows that are working and some rows that are not? Can you describe the logic that your formula is supposed to be using?

Carli B.

The section that says Compliance Thematic Review is working as intended, the purpose of the formula is to say if a certain risk activity is within the designated parameter the Actual Cycle Time Health needs to change to red, yellow, or green. The parameters are different for the various risk activities i.e. red is greater than 90 days for some but greater than 30 days for others

Paul Newcome

Ok. I understand the high-level logic. What I need to know are all the details. What are all of the variables and how should they impact each of the different outputs?

Carli B.

If Risk Activity is Validations - Issue Validations actual cycle time is between 0-22 days green, if actual cycle time health is between 23-30 days yellow and if cycle time health is more than 31 days red. If Risk Activity is Validations - Regulatory Issue actual cycle time is between 0-22 days green, if actual cycle time health is between 23-30 days yellow and if cycle time health is more than 31 days red. If Risk Activity is Compliance Thematic Review actual cycle time is between 0-75 days green, if actual cycle time health is between 76-90 days yellow and if actual cycle time health is more than 90 days red. If Risk Activity is Control Testing actual cycle time is between 0-75 days green, if actual cycle time health is between 76-90 days yellow and if actual cycle time health is more than 90 days red.

Paul Newcome

Ok. Give this a try:

Carli B.

The updated formula you provided worked, thank you so much!

Paul Newcome

Happy to help. 👍️

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.