Project Health Formula

Tony Fronza

@Genevieve P. my mistake! I forgot to adjust the formula for my column names. It worked! Thank you SO much.

Tony Fronza

@Genevieve P. Sorry, one last thing! How do I modify the formula? If I want my health to appear gray for the topmost task (i.e., the overall project health) if the overall status is on hold or canceled? Currently, the formula you gave me shows the Health as Red if the topmost status is Canceled or On hold (but it shows tasks that are canceled or on hold as gray). I included a screenshot below (top right cell is red but should be gray). Thank you!

Genevieve P.

Hi @Tony Fronza

I'm glad to hear it's working for you!

Sure, no problem. You can add this as criteria in your first IF statement for Parent rows:

IF(OR(AND([Start Date]@row = "", [Projected End Date]@row = ""), Status@row = "Cancelled"), "Gray",

=IF(COUNT(ANCESTORS(Task@row)) = 0, IF(OR(AND([Start Date]@row = "", [Projected End Date]@row = ""), Status@row = "Cancelled"), "Gray", IF(OR(AND([% Complete]@row < 0.5, [Projected End Date]@row < TODAY(30)), AND(Status@row <> "Complete", [Projected End Date]@row <= TODAY())), "Red", IF(AND([% Complete]@row < 0.75, [Projected End Date]@row < TODAY(30)), "Yellow", IF(OR([% Complete]@row > 0.75, [Projected End Date]@row >= TODAY(30)), "Green")))), IF(Status@row = "at risk", "Red", IF(OR(Status@row = "On Hold", Status@row = "Cancelled"), "Gray", IF(AND([Projected End Date]@row = "", Status@row = ""), "", IF(OR(Status@row = "Complete", [Projected End Date]@row > TODAY(7)), "Green", IF((TODAY() - [Projected End Date]@row > 0), "Red", IF(OR(Status@row = "Not Started", Status@row = "In Progress", Status@row = ""), "Yellow", "")))))))

Cheers,
Genevieve

Tony Fronza

Thank you so much @Genevieve P. ! Your assistance has been invaluable 😊

DiDL

@Genevieve P. What would be the best formula to get something similar set up for this sheet. I am struggling immensely trying to find a formula that works.

Genevieve P.

Hi @DiDL

The formula will change based on two things:

• Your column names
• Your personal criteria for each "status"

Can you paste the formula that you've tried, and explain what it is you want to happen?

Cheers,
Genevieve