# Heath Calculation based on Critical Path Health

Options
edited 12/09/19

Hello!

I'm wanting to calculate the health of my project based on the health of any task that is on the critical path.

I have seen the blog post about being able to create a filter that is able to show the critical patch, but is there a way to derive via a formula if the task is on the critical path or not? I would probably use a checkbox column and have a simple formula such as IF[Task]@row = "Critical Path", "True", "False")... Then I could integrate this into my health formulas.

I'm assuming no since I've not been able to find a lick of documentation with this mentioned... Figured I would poll the community.

Zach

• ✭✭✭✭✭✭
Options

Hi Zach,

Unfortunately, as far as I know, it's not possible at the moment, but it's an excellent idea!

Please submit an Enhancement Request when you have a moment.

Hope that helps!

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• Options

Hi Zach,

You can use this formula for checking the health of a TASK in the SHEET. You will have to incorporate this with your Critical Path Logic to get what you want. This is just a RAG status for the task. Please Create a NEW Column called "RAG" and enter this formula in CELL RAG1 , then copy drag down.

IF([% Complete]1 = 1, "Complete",

IF(AND(Finish1 >= TODAY(), [% Complete]1 = 0), "Not Started",

IF(AND(Finish1 < TODAY() + 7, [% Complete]1 < 1, [% Complete]1 >= 0.8), "Yellow",

IF(AND(Finish1 < TODAY() + 7, [% Complete]1 < 0.8), "Red", IF(AND(Finish1 > TODAY() + 60, [% Complete]1 > 0), "Green",

IF(AND(Finish1 > TODAY(), Start1 > TODAY(), [% Complete]1 > 0), "Green",

IF(AND(Finish1 > TODAY(), Start1 > TODAY(), [% Complete]1 = 0), "NotStarted", "Green"))))))), "")

• ✭✭✭✭✭✭
Options

Andree is correct. There is no way to directly reference the Critical path via a specific reference that could be included in a formula or function.

I reached out to SS about this very thing some time back. The two options you have are to either create some formula that spells out the critical path or manually mark each row that is on it.

That particular project unfortunately got put on the back burner for me, so I haven't been able to work on writing something out to automatically check a box based on critical path criteria yet.