Formula Issue
Hi All- I am working on a complex formula where I would like the Health column to update color based on specific Statuses. Then if the status is "In Progress", I want the Health column to update color based on the Time Elapsed and Percent Complete. The issue seems to be happening within the In Progress part of the formula where it is returning Incorrect Argument, blank, or Unparseable for any statuses where In Progress is selected. This is my formula:
=IF(ISBLANK([Target Due]@row), "Gray", IF(OR(Status@row = "On Hold", Status@row = "Canceled"), "Gray", IF(Status@row = "Complete", "Green", IF(AND(Status@row = "Not Started", TODAY() > [Target Start]@row), "Red", IF(AND(Status@row = "Not Started", TODAY() <= [Target Start]@row), "Gray", IF(AND(Status@row = "In Progress", ISNUMBER([Elapsed Time]@row), ISNUMBER([Percent Complete]@row)), IF(AND([Elapsed Time]@row > 0, [Elapsed Time]@row <= 25), IF([Percent Complete]@row > 20, "Green", IF(AND([Percent Complete]@row >= 15, [Percent Complete]@row <= 20), "Yellow", "Red"))), IF(AND([Elapsed Time]@row > 25, [Elapsed Time]@row <= 50), IF([Percent Complete]@row > 45, "Green", IF(AND([Percent Complete]@row >= 40, [Percent Complete]@row <= 45), "Yellow", "Red"))), IF(AND([Elapsed Time]@row > 50, [Elapsed Time]@row <= 75), IF([Percent Complete]@row > 70, "Green", IF(AND([Percent Complete]@row >= 65, [Percent Complete]@row <= 70), "Yellow", "Red")))))))))
Any help would be appreciated!
Answers
-
@kmercer The issue is actually with this portion of the formula
IF(AND([Elapsed TIme]@row > 50, [Elapsed TIme]@row <= 75), IF([Percent Complete]@row > 70, "Green", IF(AND([Percent Complete]@row >= 65, [Percent Complete]@row <= 70), "Yellow", "Red")))))))))
It is falling outside of your nested if statements.
I moved its location. See if this achieves what it is your looking for.
=IF(ISBLANK([Target Due]@row), "Gray", IF(OR(Status@row = "On Hold", Status@row = "Canceled"), "Gray", IF(Status@row = "Complete", "Green", IF(AND(Status@row = "Not Started", TODAY() > [Target Start]@row), "Red", IF(AND(Status@row = "Not Started", TODAY() <= [Target Start]@row), "Gray", IF(AND(Status@row = "In Progress", ISNUMBER([Elapsed TIme]@row), ISNUMBER([Percent Complete]@row)), IF(AND([Elapsed TIme]@row > 0, [Elapsed TIme]@row <= 25), IF([Percent Complete]@row > 20, "Green", IF(AND([Percent Complete]@row >= 15, [Percent Complete]@row <= 20), "Yellow", "Red"))), IF(AND([Elapsed TIme]@row > 25, [Elapsed TIme]@row <= 50), IF([Percent Complete]@row > 45, "Green", IF(AND([Percent Complete]@row >= 40, [Percent Complete]@row <= 45), "Yellow", "Red")), IF(AND([Elapsed TIme]@row > 50, [Elapsed TIme]@row <= 75), IF([Percent Complete]@row > 70, "Green", IF(AND([Percent Complete]@row >= 65, [Percent Complete]@row <= 70), "Yellow", "Red"))))))))))
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
-
Thanks for the quick response @Mark.poole! The corrections helped resolve the errors, but I am still getting blanks and incorrect return of colors on some.
-
Can you provide me all of the different status and colors? I am working on streamlining your formula for you. That will also correct several problems thats leading to the blanks. The reason is there is a fall statement some where in the formula that is a blank.
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
-
Sure thing. Thanks!!
Statuses are:
Not Started
In Progress
On Hold
Canceled
CompleteSymbol/Colors are:
Red, Yellow, Green, Gray
-
Please describe each situation you want to be grey, Green, Red or yellow. in plain text. I just want to be sure i cover each situation.
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
-
I think I have the logic built out here where it makes sense. Open to suggestions, if it does not.
Explanation
Result (Color) for Health
If Target Due is blank
Gray
If Status is On Hold or Canceled
Gray
If Status is Complete
Green
If Status is Not Started and the current date is past the Target Start date
Red
If Status is Not Started and the current date is on or before the Target Start date
Gray
If Status is In Progress…. Follow the below
Consider reference Elapsed Time and Percent Complete to change the Colors as follows:
If Elapsed Time is between 0-25%
Percent Complete must be >20% for green, between 15-20% for yellow, and below 15% for red.
If Elapsed time is between 25-50%
Percent Complete must be >45% for green, between 40-45% for yellow, and below 40% for red.
If Elapsed time is between 50-75%
Percent Complete must be >70% for green, between 65-70% for yellow, and below 65% for red.
If Elapsed Time is between 75-100%
Percent Complete must be >90% for green, between 80-90% for yellow, and below 80% for red.
If Elapsed Time is 100%
Percent Complete must be 100% for green otherwise make it red
-
Well part of the issue is % is in decimal format. So instead of 75 it would be .75.
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
-
=IF(OR(ISBLANK([Target Start]@row), Status@row = "On Hold", Status@row = "Canceled", AND(Status@row = "Not Started", TODAY() <= [Target Start]@row)), "Gray", IF(OR(Status@row = "Complete", AND([Percent Complete]@row = 1, [Elapsed TIme]@row = 1)), "Green", IF([Elapsed TIme]@row > 0.75, IF([Percent Complete]@row > 0.9, "Green", IF([Percent Complete]@row > 0.8, "Yellow", "Red")), IF(AND([Elapsed TIme]@row <= 0.75, [Elapsed TIme]@row > 0.5), IF([Percent Complete]@row > 0.7, "Green", IF([Percent Complete]@row > 0.65, "Yellow", "Red")), IF(AND([Elapsed TIme]@row <= 0.5, [Elapsed TIme]@row > 0.25), IF([Percent Complete]@row > 0.45, "Green", IF([Percent Complete]@row >= 0.4, "Yellow", "Red")), IF([Elapsed TIme]@row >= 0, IF([Percent Complete]@row > 0.2, "Green", IF([Percent Complete]@row > 0.15, "Yellow", "Red"))))))))
If you would rather not use decimals in elapsed time. to show the % you can change them all to whole numbers to match yours.
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
-
I am almost there….. the above works! I noticed one more criteria that I need to add. If the status is In Progress and the current date is on or before the Target Start date, I want it to turn gray. I tried adding it to the end to default to gray as the fallback if none of the conditions are met, but it remains as red. Row 11 is what I am looking at.
=IF(OR(ISBLANK([Target Start]@row), Status@row = "On Hold", Status@row = "Canceled", AND(Status@row = "Not Started", TODAY() <= [Target Start]@row)), "Gray", IF(OR(Status@row = "Complete", AND([Percent Complete]@row = 1, [Elapsed Time]@row = 1)), "Green", IF([Elapsed Time]@row > 0.75, IF([Percent Complete]@row > 0.9, "Green", IF([Percent Complete]@row > 0.8, "Yellow", "Red")), IF(AND([Elapsed Time]@row <= 0.75, [Elapsed Time]@row > 0.5), IF([Percent Complete]@row > 0.7, "Green", IF([Percent Complete]@row > 0.65, "Yellow", "Red")), IF(AND([Elapsed Time]@row <= 0.5, [Elapsed Time]@row > 0.25), IF([Percent Complete]@row > 0.45, "Green", IF([Percent Complete]@row >= 0.4, "Yellow", "Red")), IF([Elapsed Time]@row >= 0, IF([Percent Complete]@row > 0.2, "Green", IF([Percent Complete]@row > 0.15, "Yellow", "Red")), "Gray"))))))
-
@kmercer
I added it near the top under the initial Gray if statement. That should fix it for you=IF(OR(ISBLANK([Target Start]@row), Status@row = "On Hold", Status@row = "Canceled", AND(OR(Status@row = "Not Started", Status@row = "In Progress"), TODAY() <= [Target Start]@row)), "Gray", IF(OR(Status@row = "Complete", AND([Percent Complete]@row = 1, [Elapsed TIme]@row = 1)), "Green", IF([Elapsed TIme]@row > 0.75, IF([Percent Complete]@row > 0.9, "Green", IF([Percent Complete]@row > 0.8, "Yellow", "Red")), IF(AND([Elapsed TIme]@row <= 0.75, [Elapsed TIme]@row > 0.5), IF([Percent Complete]@row > 0.7, "Green", IF([Percent Complete]@row > 0.65, "Yellow", "Red")), IF(AND([Elapsed TIme]@row <= 0.5, [Elapsed TIme]@row > 0.25), IF([Percent Complete]@row > 0.45, "Green", IF([Percent Complete]@row >= 0.4, "Yellow", "Red")), IF([Elapsed TIme]@row >= 0, IF([Percent Complete]@row > 0.2, "Green", IF([Percent Complete]@row > 0.15, "Yellow", "Red"))))))))
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
-
@Mark.poole- Thank you so much! I struggled with this for days and tried researching, etc. before asking in this community. So grateful for the help and learning opportunity!
-
@kmercer Absolutely. No problem at all. Just remember the way the If statements work is it will search for the first true statement then continue on.
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 67.2K Get Help
- 452 Global Discussions
- 155 Industry Talk
- 505 Announcements
- 5.4K Ideas & Feature Requests
- 85 Brandfolder
- 156 Just for fun
- 81 Community Job Board
- 514 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 308 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
