Project Health Formula

Options
ecarrero
ecarrero ✭✭
in Formulas and Functions

Hi there,

My formula seems to be broken. Any ideas?

=IF(AND(OR(Status@row = "Not Started", Status@row = "In Progress"), ISBLANK([Projected End Date]@row)), "Yellow", IF(Status@row = "Complete", "Green", IF((TODAY() - [Projected End Date]@row > 0), "Red", "")))

  • Project Health turns "Green" for any row that has a Status of "Complete" or if the Projected End Date is 1 week out or further
  • Project Health turns "Yellow" for any row that does not have a Status of "Complete", "On Hold", or "Canceled", and the Projected End Date is within the next 7 days
  • Project Health turns "Gray" for any row that has a Status of "On Hold" or "Canceled"
  • Project Health turns "Red" for any row that does not have a Status of "Complete", "On Hold", or "Canceled", and the Projected End Date is within the next 3 days or is in the past
  • Project Health is “blank” for any row where the Status and Target End Date cells are empty
  • Additionally, it should show the average health of its children's tasks.
image.png


· Share on FacebookShare on Twitter

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    Ok great! Add this to your formula then:

    IF(Status@row = "at risk", "Red"


    I would suggest putting it at the beginning of your child row statements so that this will be the first criteria looked at:

    =IF(COUNT(ANCESTORS(Task@row)) = 0, IF(AND([Start Date]@row = "", [Projected End Date]@row = ""), "Gray", IF(AND([% Complete]@row < 0.5, [Projected End Date]@row < TODAY(30)), "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

    · Share on FacebookShare on Twitter
«1

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Are you getting an error or an unexpected output?

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

    · Share on FacebookShare on Twitter
  • ecarrero
    ecarrero ✭✭
    Options

    @Paul Newcome The green project health one is the only one working. The rest are not, and the parent child relationship is not reflected in any.

    · Share on FacebookShare on Twitter
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    How would you expect the child average to work? What if there are two red and two green? Can you spell out in detail how that piece would work similar to the level of detail you provided for the child rows in your original post?

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

    · Share on FacebookShare on Twitter
  • ecarrero
    ecarrero ✭✭
    Options

    @Paul Newcome

    The parent row will remain green unless, it has one or more children rows with yellow or red categories.

    • Project Health turns "Green" for any row that has a Status of "Complete" or if the Projected End Date is 1 week out or further
    • Project Health turns "Yellow" for any row that does not have a Status of "Complete", "On Hold", or "Canceled", and the Projected End Date is within the next 7 days
    • Project Health turns "Red" for any row that does not have a Status of "Complete", "On Hold", or "Canceled", and the Projected End Date is within the next 3 days or is in the past

    Ideally, I'd also have a overall project health formula so I can feed into a dashboard.

    Looking at row 1 (start and projected end date):

    Green if the overall project completion % is <75, within 1 month of projected end date.

    Yellow if the overall project completion % is <50, within 1 month of projected end date.

    Red if the overall project completion % is >50, within 1 month of projected end date.

    Gray if the project start date and end date is blank.


    image.png


    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @ecarrero

    Try this:

    =IF(COUNT(ANCESTORS(Task@row)) = 0, IF(AND([Start Date]@row = "", [Projected End Date]@row = ""), "Gray", IF(AND([% Complete]@row < 0.5, [Projected End Date]@row < TODAY(30)), "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(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", ""))))))



    I'll break it down for you:

    =IF(COUNT(ANCESTORS(Task@row)) = 0,

    If the current row has NO Parent rows (so this only applies to your Top Row), then do the following:

    IF(AND([Start Date]@row = "", [Projected End Date]@row = ""), "Gray", IF(AND([% Complete]@row < 0.5, [Projected End Date]@row < TODAY(30)), "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")))),

    Otherwise, do the following:

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



    These are the statements in the formula above:

    PARENT ROW FORMULA

    • If the Parent Start Date is empty and the End Date is empty, return blank
    • If the % Complete is less than 50% and the End Date is in the next 30 days, return Red
    • If the % Complete is less than 75% and the End Date is in the next 30 days, return Yellow
    • If the % Complete is greater than 75% OR the End Date is more than 30 days in the future, return Green

    ALL OTHER ROWS

    • If the Status is either "On Hold" or "Cancelled", return Gray
    • If the End Date and the Status are both blank, then return blank
    • If either the Status is "Complete" or the End Date is greater than 7 days from today, return Green
    • If the End Date is in the Past, return Red
    • If either the Status is "Not Started", "In Progress" or is blank (but has an End Date), return "Yellow"


    Let me know if this fits all of your criteria or not!

    Cheers,

    Genevieve

    · Share on FacebookShare on Twitter
  • ecarrero
    ecarrero ✭✭
    Options

    Thank you! This is working, I am just noticing a few one-offs that are appearing blank and I'm not sure why?

    image.png

    Projected end date and data both have data.

    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @ecarrero

    What is that Status for that row?

    There's only one statement that takes into account End Dates that are less than 7 days in the future (and not in the past), but the status needs to be "Not Started", "In Progress", or blank to be Yellow.

    · Share on FacebookShare on Twitter
  • ecarrero
    ecarrero ✭✭
    Options

    They are both in the "at risk" status.

    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @ecarrero

    What colour would you like that status to show? Red or Yellow?

    · Share on FacebookShare on Twitter
  • ecarrero
    ecarrero ✭✭
    Options

    Red

    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    Ok great! Add this to your formula then:

    IF(Status@row = "at risk", "Red"


    I would suggest putting it at the beginning of your child row statements so that this will be the first criteria looked at:

    =IF(COUNT(ANCESTORS(Task@row)) = 0, IF(AND([Start Date]@row = "", [Projected End Date]@row = ""), "Gray", IF(AND([% Complete]@row < 0.5, [Projected End Date]@row < TODAY(30)), "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

    · Share on FacebookShare on Twitter
  • ecarrero
    ecarrero ✭✭
    Options

    THANK YOU SO MUCH!

    · Share on FacebookShare on Twitter
  • Tony Fronza
    Tony Fronza ✭✭✭✭
    Options

    @Genevieve P. hello! I love this formula and started using it. However, I have a question - how would I add language to show a red status if the project end date passes and is not complete?

    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hey @Tony Fronza

    It's great to hear a formula from a year ago is helping you today! 🙂

    You can add an OR statement to the first IF where it says to generate Red.

    Here's the original:

    IF(AND([% Complete]@row < 0.5, [Projected End Date]@row < TODAY(30)), "Red",

    Updated:

    IF(OR(AND([% Complete]@row < 0.5, [Projected End Date]@row < TODAY(30)), AND(Status@row <> "Complete", [Projected End Date]@row <= TODAY())), "Red",

    For the child rows, this is already outlined. I've bolded the two sections below:

    =IF(COUNT(ANCESTORS(Task@row)) = 0, IF(AND([Start Date]@row = "", [Projected End Date]@row = ""), "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

    · Share on FacebookShare on Twitter
  • Tony Fronza
    Tony Fronza ✭✭✭✭
    Options

    Thanks, @Genevieve P.! I tried copying and pasting the updated formula above but received a formula syntax error.

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!