Project Health Formula for Using Symbols

Jessica Lake Days

I'm having a lot of trouble with creating a formula that shows project and task health with RYG symbols based on the following criteria:

My columns are Status, Start Date, End Date and Date Completed

Green:

If Status is "Not Started" and Start Date is today or only 3 days past.

If Status is "In Progress" and End Date is today or no more than 3 days past.

If Status is "Complete" and Date Completed is less than or equal to 3 days past End Date

Yellow: if Status is "Not Started" and Start Date is more than 3 days past

If Status is "In Progress" and End Date is more than 3 days past.

If Status is "Complete" and Date Completed is more than 3 days past End Date.

Red: if Status is "Not Started" and Start Date is more than 7 days past.

If Status is "In Progress" and End Date is more than 7 days past.

If Status is "Complete" and Date Completed is more than 3 days past End Date.

If the Status is On Hold, or Start Date is Blank then it should be Gray.

Parent rows will also need to average of non-Gray children.

I can get the Parent rows to work with this formula:

=IF(COUNT(CHILDREN()) > 0, IF(COUNTIF(CHILDREN(), "Gray") = COUNT(CHILDREN()), "Gray", IF((COUNTIF(CHILDREN(), "Yellow") + (COUNTIF(CHILDREN(), "Green") * 2)) / COUNTIF(CHILDREN(), <>"Gray") <= 0.5, "Red", IF((COUNTIF(CHILDREN(), "Yellow") + (COUNTIF(CHILDREN(), "Green") * 2)) / COUNTIF(CHILDREN(), <>"Gray") <= 1.5, "Yellow", IF((COUNTIF(CHILDREN(), "Yellow") + (COUNTIF(CHILDREN(), "Green") * 2)) / COUNTIF(CHILDREN(), <>"Gray") <= 2.5, "Green", "Gray"))))

But whenever I try to add any of the other criteria for the children I will get #Unparseable or #Incorrect Argument Set errors. I feel like I may have been too ambitious, but if I can get this column to work it will be much easier to show the health of my projects and with my team members.

Any help is much appreciated!

Matt Lynn-PCG

@Jessica Lake Days , no you weren't too ambitious. If you can think it logically it can (almost) always be formulated.

1.) Add a column to your sheet (hidden helper) and the name is "C". This stand for children. In that column put a column formula " =count(children()) ". This will simply count the amount of children at every row. We'll use this in the next formula which is the one your looking for.

Use this formula for your colors/RYGG column.

=IF(C@row > 0, IF(COUNT(CHILDREN()) > 0, IF(COUNTIF(CHILDREN(), "Gray") = COUNT(CHILDREN()), "Gray", IF((COUNTIF(CHILDREN(), "Yellow") + (COUNTIF(CHILDREN(), "Green") * 2)) / COUNTIF(CHILDREN(), <>"Gray") <= 0.5, "Red", IF((COUNTIF(CHILDREN(), "Yellow") + (COUNTIF(CHILDREN(), "Green") * 2)) / COUNTIF(CHILDREN(), <>"Gray") <= 1.5, "Yellow", IF((COUNTIF(CHILDREN(), "Yellow") + (COUNTIF(CHILDREN(), "Green") * 2)) / COUNTIF(CHILDREN(), <>"Gray") <= 2.5, "Green", "Gray"))))), IF(Status@row = "On Hold", "Gray", IF(OR(AND(Status@row = "Not Started", ([Start Date]@row - TODAY()) > -3, [Start Date]@row <= TODAY()), AND(Status@row = "In Progress", [End Date]@row >= TODAY(), ([End Date]@row - TODAY()) <= 3), AND(Status@row = "Complete", [Date Completed]@row <= ([End Date]@row + 3))), "Green", IF(OR(AND(Status@row = "Not Started", (TODAY() - [Start Date]@row) > 3), AND(Status@row = "In Progress", (TODAY() - [End Date]@row) > 3), AND(Status@row = "Complete", ([Date Completed]@row - [End Date]@row) > 3)), "Yellow", IF(OR(AND(Status@row = "Not Started", (TODAY() - [Start Date]@row) > 7), AND(Status@row = "In Progress", (TODAY() - [End Date]@row) > 7), AND(Status@row = "Complete", ([Date Completed]@row - [End Date]@row) > 7)), "Red", "")))))

This is roughly 1240 characters so you're well within the 4000 character limit.

Jessica Lake Days

I tried the formula and it has made the many of the columns Yellow when they should be Green, Red, or Gray.