Formula Health/Risk - Need Help

cpauciello

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.

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?

cpauciello

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

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.

cpauciello

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

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

(edited for punctuation)

cpauciello

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

What else?

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?

cpauciello

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

@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?