Formula Health/Risk - Need Help

I'm going to apologize in advance that this questions is to going to be basic. I'm just learning smartsheets and formulas. I've tried to read other posts and answers and I still can't figure it out.

Health Balls-

If %Complete is 100% = "Green"

If %Complete does not equal 100% and Today < Actual End Date= "Red

If %Complete <50% and actual end date is <14 days away = "Yellow"

If Status is "Not Started" and Actual Start Date is = "today" = "Red"

If Status@row is "Not Started" and Actual Start Date is >"today" = "Grey"


I also can't get my risk formula to work?

=IF(AND([Actual Start Date]@row <=Today(), [% Complete]@row; >1, OR[Status}@row <> "On Hold")),1,0)


Thank you in advance for any help.

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    There are a number of variations not yet accounted for such as...


    What if it is greater than 50% but less than 14 days away?

    What if it is less than 100% but the End Date is in the future?

    What if the Status is "Not Started" and the Start Date is in the past?


    If you are able to spell out what colors you want for every possible variation, we may be able to help put something together for you.


    And what exactly are you hoping to accomplish with your Risk formula?

  • Thank you for your response Paul. Yes, I can see I was missing some options now! As I mentioned I'm new to this... LOL Lets add the below:

    Health Balls-

    If %Complete is 100% = "Green"

    If %Complete does not equal 100% and Today < Actual End Date= "Red

    If %Complete <50% and actual end date is <14 days away = "Yellow"

    If % Complete is <50% and actual end date is >14 days away = "Red"

    If Status is "Not Started" and Actual Start Date is = "today" = "Red"

    If Status@row is "Not Started" and Actual Start Date is <"today" = "Grey"

    If Status@row is "Not Started" and actual Start date is > = "Red"


    What if it is less than 100% but the End Date is in the future? I think that we have covered these above with the less than 50% and end date in future?

    For the Risk Formula- I'm trying to say apply a risk flag if actual start date is < today and % Complete is less than 1% OR if status at row is on hold. Does that make sense?

    Thanks in advance for your help!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    What if it is 75% and the end date is / is not within the next 14 days, etc.? Right now you would only have Red if the end date is in the past, but if the end date is not in the past and the % is between 51% and 99% then the cell will be blank.

  • ok - so we should add that one too.

    If %complete is between 51%- 99%- and end date is past- should be "red".

    Is that all of them? Thank you for your help.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    edited 02/22/23

    What if the end date is not in the past? Right now that would be blank.


    (edited for punctuation)

  • Ok If %complete is between 51%- 99%- and end date is in future - should be "yellow"


    What else?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    So far this is what we have...


    (NOTE: As you move down the list, assume that the rules above the rule you are reading are all false)

    % Complete = 100% --> Green

    Start Date in the future --> Gray

    End Date in the past --> Red

    Status = "Not Started" and Start Date is today or in the past --> Red

    % Complete is less than or equal to 50% and End Date is in the next 14 days --> Red

    % Complete is less than or equal to 50% and End Date is more than 14 days away --> Yellow

    % Complete is greater than 50% --> Yellow


    Does the above look right to you?

  • Yes - i think so. Maybe just one more:

    % Complete is more than or equal to 50% and End Date is more than 14 days away --> green

    And tweaking the last one:

    %% Complete is greater than 50% and End Date is more than 30 days away --> Yellow


    Thanks

    Cat

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @cpauciello Those last two honestly don't make much sense in that order.


    If the % Complete is at least 50% and the end date is 90 days away then we want yellow, but once the end date is within two weeks we want to output green? Or would you rather have it so that it is green when at least 30 days away and yellow when less than 30 days away?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!