Formula for overall health using symbols

Options

Hi All

I am looking for a formula (or any other way) that would update my Overall Health column based on the status of my 5 status categories.

Symbols I am using are:

Green - Project is running on time scope and budget etc

Yellow - 2 or more of the 5 categories are showing yellow

Red - 2 or more of the 5 categories are showing red (even if more are yellow, red should appear)

Blue - Project Completed



Jeffrey McMillan CAPM, PMP

Project Manager, Financial Applications ERP

Best Answer

  • SmartLew
    SmartLew ✭✭✭✭
    Answer ✓
    Options

    ok- in your first post you said

    "Yellow - 2 or more of the 5 categories are showing yellow"

    but above you say if 2 or fewer are yellow then green. Should it be less than 2 yellow is green?

    See how this one works for you. Play around with the combos see if it works.

    =IF(COUNTIF([stakeholder health]@row:[resourcing health]@row, "Blue") = 5, "Blue", IF(COUNTIF([stakeholder health]@row:[resourcing health]@row, "Red") > 1, "Red", IF(COUNTIF([stakeholder health]@row:[resourcing health]@row, "Yellow") > 1, "Yellow", "Green")))

    Not sure how you want to represent different combos such as , 2 x blue, 1 x red, 1 x yellow, 1 x green? Currently this will show Green.

    Let me know!

    I'm passionate about helping you leverage the truly awesome power of smartsheet!

    https://www.fiverr.com/smartlew

Answers

  • SmartLew
    SmartLew ✭✭✭✭
    Options

    What are the criteria for turning the overall health column Green or Blue?

    What indicates project is complete?

    What makes overall health green?

    I'm passionate about helping you leverage the truly awesome power of smartsheet!

    https://www.fiverr.com/smartlew

  • Jeffrey McMillan
    Options

    A blue symbol indicated complete. If all the health statuses are blue then the Overall health should be blue to indicate complete

    Overall heath should be green if all status' are green, 2 or fewer of the status are showing yellow.

    Jeffrey McMillan CAPM, PMP

    Project Manager, Financial Applications ERP

  • SmartLew
    SmartLew ✭✭✭✭
    Answer ✓
    Options

    ok- in your first post you said

    "Yellow - 2 or more of the 5 categories are showing yellow"

    but above you say if 2 or fewer are yellow then green. Should it be less than 2 yellow is green?

    See how this one works for you. Play around with the combos see if it works.

    =IF(COUNTIF([stakeholder health]@row:[resourcing health]@row, "Blue") = 5, "Blue", IF(COUNTIF([stakeholder health]@row:[resourcing health]@row, "Red") > 1, "Red", IF(COUNTIF([stakeholder health]@row:[resourcing health]@row, "Yellow") > 1, "Yellow", "Green")))

    Not sure how you want to represent different combos such as , 2 x blue, 1 x red, 1 x yellow, 1 x green? Currently this will show Green.

    Let me know!

    I'm passionate about helping you leverage the truly awesome power of smartsheet!

    https://www.fiverr.com/smartlew

  • Jeffrey McMillan
    Options

    That works thank you!!!

    Jeffrey McMillan CAPM, PMP

    Project Manager, Financial Applications ERP

  • SmartLew
    SmartLew ✭✭✭✭
    Options

    @Jeffrey McMillan Great! Happy to help

    I'm passionate about helping you leverage the truly awesome power of smartsheet!

    https://www.fiverr.com/smartlew