How to a build a formula to change Overall Project Health if Color codes in another column(s)

Options

So I've been asked to create a forumla based solution that does changes the color code in 'Overall Project Health Status' column if any changes occur in the Scope, Schedule,Budget, Client Satisfaction columns (see above) using the following criteria (see above screen shot) for criteria. Not really good with formulas in Smartsheet, so any help I can get to not waste alot of time 'experimenting' would be great.

Thanks

Best Answers

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

    Hi @bhuyler

    Try this and see if it suits all of your possible combinations:

    =IF(OR(COUNTIF(Scope@row:[Client Satisfaction]@row, "Red") >= 1, COUNTIF(Scope@row:[Client Satisfaction]@row, "Yellow") = 4), "Red", IF(COUNTIF(Scope@row:[Client Satisfaction]@row, "Yellow") >= 2, "Yellow", "Green"))

    I'll break down each of the parts of the formula for you.

    Red Instruction

    You have two instances where the Overall could be "Red" - if there's even just one Red status ball anywhere in the row, OR if there's 4 yellow status balls.

    First we count how many Reds there are across the row:

    COUNTIF(Scope@row:[Client Satisfaction]@row, "Red") >= 1

    then we count the Yellows

    COUNTIF(Scope@row:[Client Satisfaction]@row, "Yellow") = 4

    and we put that in an OR Function:

    =IF(OR(COUNTIF(Scope@row:[Client Satisfaction]@row, "Red") >= 1, COUNTIF(Scope@row:[Client Satisfaction]@row, "Yellow") = 4), "Red"


    Yellow Instruction

    Next we're looking to see if there are 2 ore more yellows in the row, to then turn yellow. We don't have to specify what happens if there are 4 yellows, since we wrote that in the Red rule and the Red rule comes first.

    IF(COUNTIF(Scope@row:[Client Satisfaction]@row, "Yellow") >= 2, "Yellow",


    Green Rule

    Anything else will return Green.


    I will note that we didn't include anything in for if there are blank cells, so if there is only 1 Green status ball or 1 Yellow status ball and all the others are blank, it will return Green as the overall health.


    Let me know if this works for you!

    Cheers,

    Genevieve

  • bhuyler
    bhuyler ✭✭
    Answer ✓
    Options

    @Dave Charboneau thanks. The link appears to be broken to the training however.

Answers

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

    Hi @bhuyler

    Try this and see if it suits all of your possible combinations:

    =IF(OR(COUNTIF(Scope@row:[Client Satisfaction]@row, "Red") >= 1, COUNTIF(Scope@row:[Client Satisfaction]@row, "Yellow") = 4), "Red", IF(COUNTIF(Scope@row:[Client Satisfaction]@row, "Yellow") >= 2, "Yellow", "Green"))

    I'll break down each of the parts of the formula for you.

    Red Instruction

    You have two instances where the Overall could be "Red" - if there's even just one Red status ball anywhere in the row, OR if there's 4 yellow status balls.

    First we count how many Reds there are across the row:

    COUNTIF(Scope@row:[Client Satisfaction]@row, "Red") >= 1

    then we count the Yellows

    COUNTIF(Scope@row:[Client Satisfaction]@row, "Yellow") = 4

    and we put that in an OR Function:

    =IF(OR(COUNTIF(Scope@row:[Client Satisfaction]@row, "Red") >= 1, COUNTIF(Scope@row:[Client Satisfaction]@row, "Yellow") = 4), "Red"


    Yellow Instruction

    Next we're looking to see if there are 2 ore more yellows in the row, to then turn yellow. We don't have to specify what happens if there are 4 yellows, since we wrote that in the Red rule and the Red rule comes first.

    IF(COUNTIF(Scope@row:[Client Satisfaction]@row, "Yellow") >= 2, "Yellow",


    Green Rule

    Anything else will return Green.


    I will note that we didn't include anything in for if there are blank cells, so if there is only 1 Green status ball or 1 Yellow status ball and all the others are blank, it will return Green as the overall health.


    Let me know if this works for you!

    Cheers,

    Genevieve

  • bhuyler
    bhuyler ✭✭
    Options

    Hi Genevieve,


    Yes that was perfect. My colleague and I figured the IF arugment to find all reds, it was trying to figure out the COUNTIF logic that was giving me and her a hard time.


    Thank You!

  • Dave Charboneau
    Options

    This course in SmartSheet University is excellent when explaining how to set overall health for a project, activity or task based on criteria for it's children() ... https://smartu.smartsheet.com/series/core-features/taking-formulas-to-the-next-level

  • bhuyler
    bhuyler ✭✭
    Answer ✓
    Options

    @Dave Charboneau thanks. The link appears to be broken to the training however.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!