Help with this Formula

cvilla

I'm trying to seta formula for the health of the project's tasks based on certain conditions. This is the formula:

=IF(OR(AND(Status@row <> "Complete", [End Date]@row < TODAY()), AND(Status@row = "In Progress", VALUE([% Complete]@row ) < 0.3, NETDAYS(TODAY(), [End Date]@row ) <= 30), AND(Status@row = "Not Started", [Start Date]@row < TODAY())), "Red", IF(AND(Status@row = "In Progress", NETDAYS(TODAY(), [End Date]@row ) <= 30, VALUE([% Complete]@row ) < 0.5), "Yellow", IF(OR(Status@row = "Complete", AND(Status@row = "In Progress", NETDAYS(TODAY(), [End Date]@row ) > 30)), "Green", IF(Status@row = "Not Started", "Gray", ""))))

These are the conditions:

However, I also want to add that if the start date is today and the due date let's say is within 1 week, when they put the status to in progress and like 5% complete the formula comes back as red, because the person didn't complete the 20% approx. that needs to be completed based on % complete. Is there a way to add some percentages of %Complete to show this as Yellow instead of red? Also, why some tasks do not report any color even the info is filled and there is no formula errors shown?

Thank you

Jgorsich

I sometimes find it very helpful, when dealing with a complicated formula, to take a look at it in an editor program (I prefer Notepad++ as it is free and will highlight opening and closing parenthesis for you) to analyze it. I've done so and pasted in the results below, along with some notes in brackets at the start of the lines.

Regarding your second question first - why some lines are shown with no status, look at the false conclusion of D - you've told it to show you nothing if all of the tests fail. I'd presume that in the cases you are seeing nothing, it is likely something like an "In Progress" status with less than 30 days before the end date and about 60% complete, that doesn't seem to pass ANY of your criteria (Yellow requires less than 50% while green requires greater than 30 days). To fix this, modify your test conditions so that you don't have any gaps. What SHOULD the color be if there are 15 days left and the project is 75% complete?

Regarding your first question, the answer is pretty similar - right now you have "Red" being less than 30% and less than 30 days, if you want to modify this just modify the applicable line to match your criteria. You already HAVE a percentage limit on that (specifically, the second AND statement in [testA] includes the line "VALUE([% Complete]@row ) < 0.3", this is your percentage complete and means 30%), just modify it to suit your desired test.

.[ifA] =IF(
.[testA] OR(
. AND(
. Status@row <> "Complete",
. [End Date]@row < TODAY()
. ),
. AND(
. Status@row = "In Progress",
. VALUE([% Complete]@row ) < 0.3,
. NETDAYS(TODAY(), [End Date]@row ) <= 30
. ),
. AND(
. Status@row = "Not Started",
. [Start Date]@row < TODAY()
. )
. ),
.[trueA] "Red",
.[falseA,ifB] IF(
.[testB] AND(
. Status@row = "In Progress",
. NETDAYS(TODAY(), [End Date]@row ) <= 30,
. VALUE([% Complete]@row ) < 0.5
. ),
.[trueB] "Yellow",
.[falseB, ifC] IF(
.[testC] OR(
. Status@row = "Complete",
. AND(
. Status@row = "In Progress",
. NETDAYS(TODAY(), [End Date]@row ) > 30
. )
. ),
.[trueC] "Green",
.[falseC, ifD] IF(
.[testD] Status@row = "Not Started",
.[trueD] "Gray",
.[falseD] ""
. )
. )
. )
. )