At Risk Flag Formula Help

mmac

Hello,

 

I currently am using the below formula in the At Risk column which works just fine. 

If baseline due date is 3 days out and status is not checked off as complete as well as % complete is not 100%, flag as at risk.

=IF(AND([Baseline Due Date]@row <= TODAY(3), [Baseline Due Date]@row >= TODAY(), Status@row <> "Yes", [% Complete]@row <> 1), 1, 0)

 

I'm attempting to revise formula for the below needs, but have exhausted my efforts in doing so. 

 

Baseline due date = 3 days out

Baseline due date = in the past

AND

Date received does not = A date

Status does not = Yes (Complete)

% Complete does not = 100%

=

FLAG AT RISK

Mike Wilday

Try this: =IF(AND([Baseline Due Date]@row <= TODAY(3), [Baseline Due Date]@row >= TODAY(), ISDATE([Date Received]@row, Status@row <> "Yes", [% Complete]@row <> 1), 1, 0)

Mike Wilday

Sorry - Need to edit that: 

=IF(AND([Baseline Due Date]@row <= TODAY(3), [Baseline Due Date]@row >= TODAY(), NOT(ISDATE([Date Recieved]@row)), Status@row <> "Yes", [% Complete]@row <> 1), 1, 0)

mmac

Thank you! That worked for each scenario for the exception of testing if the due date is in the past. That did not seem to flag the at risk = Yes.

 

 

 

 

mmac

In addition, I am trying to flag at risk = yes for the parent row when the # of at risk tasks is greater than 0.

Currently, the # of at risk tasks counts the sum of the children rows flagged as at risk, which I wanted to keep in place. But in addition to that, I would like to flag the parent row as at risk if the value is greater than 0.

 

 

Paul Newcome

Try removing the portion of

 

=IF(AND(........, [Baseline Due Date]@row >= TODAY(), ..........), 1, 0)

Paul Newcome

Try this one...

 

=IF(OR([# of At Risk Tasks]@row > 0, AND([Baseline Due Date]@row <= TODAY(3), NOT(ISDATE([Date Recieved]@row)), Status@row <> "Yes", [% Complete]@row <> 1)), 1, 0)

Mike Wilday

Try this one... It checks for both cases. 

=IF(AND([Baseline Due Date]@row <= TODAY(3), NOT(ISDATE([Date Recieved]@row)), Status@row <> "Yes", [% Complete]@row <> 1), 1, IF(AND([Baseline Due Date)@row > Today(), NOT(ISDATE([Date Recieved]@row)), Status@row <> "Yes", [% Complete]@row <> 1), 1,  0)

Try this one which checks for the first case, then for the second case. 

mmac

Thanks! This is helpful. It does solve for if the baseline due date is in the past to flag.

 

Two findings with this formula after having dragged down the at-risk column.

1) When baseline due date is blank, at risk is flagged. Ideally, it wouldn't be if the baseline due date is blank.

2) The # of at risk tasks at the parent level is no longer being counted when I added the suggested formula to the at risk cell linked to the parent row. In the # of at risk tasks at the parent row, in that cell, I currently have "=COUNTIF([At Risk]:[At Risk], 1)"

 

Criteria I am hoping to find in a formula is:

Parent row: sum # of at risk tasks based on children rows that are flagged as at risk. (e.g. 3)

If three tasks indented under the parent row are flagged, the sum value returned would be 3 AND the parent row would be flagged as at risk, at the project level.

 

For the children rows:

If baseline due date is not populated, do not flag at risk

If baseline due date is 3 days out, flag at risk IF status does not = yes and % complete is not 100

If baseline due date is populated and in the past, flag at risk IF status does not = yes and % complete is not 100

 

I've gone ahead and shared out the sheet for further testing/support. Thank you in advance for additional time spent in resolving this.

https://app.smartsheet.com/b/publish?EQBCT=bdf3f97a3f6843a081a13451745a1d8c&ss_v=86.1.2

mmac

Thanks, Mike! I'm eager to try that, but it's not being accepted. I corrected the spelling error in Received, but still no luck.

 

Mike Wilday

I found it. Had a parenthesis when I needed a bracket in there... Try again... this is for the children columns. 

=IF(AND([Baseline Due Date]@row <= TODAY(3), NOT(ISDATE([Date Recieved]@row)), Status@row <> "Yes", [% Complete]@row <> 1), 1, IF(AND([Baseline Due Date]@row > Today(), NOT(ISDATE([Date Received]@row)), Status@row <> "Yes", [% Complete]@row <> 1), 1, 0)

I imagaine you can add something to the front of this that will check for the parent rows.... 

mmac

Thank you, Mike!

 

The issues that I am still facing are the following:

When I paste the formula into the at-risk cell, and the baseline due date is blank, the at-risk flag is triggered when I wish it would not be.

If baseline due date is blank, at-risk flag should not be triggered.

 

Please see video example:

https://www.loom.com/share/cda3d613839f4baebc1e984a27d21572

 

1. baseline due date = blank, no at-risk flag triggered
2. baseline due date is populated and due is within the next 3 days, trigger as at-risk
3. baseline due date is in the past and the date received is left blank, trigger as at-risk
4. if % complete = 100% OR status = Yes (complete), no at-risk flag triggered

 

Paul Newcome

You can add that in an IF statement to basically say "If the Baseline Due Date is a date, then run the formula, otherwise leave blank" like this...

 

=IF(ISDATE([Baseline Due Date]@row), IF(AND([Baseline Due Date]@row <= TODAY(3), NOT(ISDATE([Date Recieved]@row)), Status@row <> "Yes", [% Complete]@row <> 1), 1, IF(AND([Baseline Due Date]@row > Today(), NOT(ISDATE([Date Received]@row)), Status@row <> "Yes", [% Complete]@row <> 1), 1, 0))

.

You can also use this same concept to check for parent rows and run your COUNTIFS if there are children and run the above if there are not.

 

=IF(COUNT(CHILDREN([Task Name Column]@row) > 0, IF(COUNTIFS(CHILDREN(), 1) > 0, 1), insert above formula here)

Paul Newcome

When trying to access the published sheet, I got the below notice. To allow access, you will need to publish the sheet and adjust the setting in the second screenshot.

 

 

Mike Wilday

Paul's rendition should work. Let us know if it doesn't. @Paul I like how you trigger the IFstatement on whether there is a date there or not. I've always just added the criteria, but it makes sense. I like it. 

Paul Newcome

Thanks, Mike.

 

I typically end up doing things this way for a few different reasons. One of those being when I am building out a multi-part formula. I personally find it easier to wrap an IF statement around things than trying to dig in and make sure I get the AND statement just right and in all of the places it needs to be.

 

It also allows for multiple layers (as is evident above). This allows me to dragfill and adjust hierarchies without having to go through and copy/paste formulas over and over again.

 

You can also layer in multiple "check points" if there happens to be similar criteria for different things.

 

=IF(this is true, IF(that is true, do that, do something else), do something completely different)

 

Being able to close an IF in the middle of a nested IF provides a ton of flexibility whereas

 

=IF(AND(this is true, that is true), do this, IF(AND(this is true, that is false), do something else, do something completely different))

 

may not seem so bad, but what if you have 5 different variables triggering off of that 1st "this is true"? Now you have 5 AND statements and need to enter "this is true" 4 more times than in the first way.

 

It is also helpful when you have a longer nested if where EVERY option has to include that one thing.

 

I have a formula that is 5 IF statements long. Some of them already have AND and OR and other various functions built in, but I don't want ANY of them to run unless this other cell contains a date, and if there is no date, I want helper text to tell people they need to enter a date.

 

Instead of slipping this ISDATE([column name]@row) into every single IF, I just wrap the whole thing in the IF(ISDATE(.....).

 

That allows me to say "if this is a date, run this complex formula, otherwise "insert text here". It is much easier than the AND AND AND AND AND being nested into every IF PLUS an additional IF being tacked on somewhere that says IF(ISBLANK(.....), "insert text here").

.

Long story short... It really helps in a lot of different ways. It just takes a slightly different mindset when building formulas.