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!

Find more posts tagged with

Accepted answers

All comments

Mark.poole

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

kmercer

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.

Mark.poole

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.

kmercer

Sure thing. Thanks!!

Statuses are:

Not Started
In Progress
On Hold
Canceled
Complete

Symbol/Colors are:

Red, Yellow, Green, Gray

Mark.poole

@kmercer

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.

kmercer

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

Mark.poole

Well part of the issue is % is in decimal format. So instead of 75 it would be .75.

Mark.poole

@kmercer

=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.

kmercer

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

Mark.poole

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

kmercer

@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!

Mark.poole

@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.

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.