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")), ""))))
Best Answer
-
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"))))
Answers
-
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?
-
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
-
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?
-
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.
-
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"))))
-
The updated formula you provided worked, thank you so much!
Help Article Resources
Categories
Check out the Formula Handbook template!