At Risk Flag & IF Function

Paul Newcome

Hahaha. No worries. I usually forget the square brackets when doing ranges.

 

[Actual Start]:Actual Start]

 

is usually how mine look the first time I type them out. No idea why. I just seem to miss that one bracket a lot.

Paul Newcome

It looks like the comma is missing after the OR statement and right before the 1, causing the #UNPARSABLE error. 

 

Zachary:

Good catch and welcome to the Community. 

brandi.meyer80376

So, I have questions. I think I need the statement to be an AND not an OR, because I would the Flag marked in the "At Risk" column when the % Complete column is less than 80% AND the "End Date" column is within 2 days of the current date.  Is that something that can be done? 

I was able to get the statement entered without an error message coming up, so thank you to everyone that has helped so far! 

My current statement looks like this:

=IF(AND([End Date]@row >= TODAY(2), AND([% Complete]@row <> "")), 1, 0)

Paul Newcome

That is correct. AND instead of OR.

brandi.meyer80376

Awesome! I got it now. Thanks so much everyone for you help. Mike, Paul, and Zachary, you guys are true MVPS!

Paul Newcome

HAHA! Happy to help! 

SamiV

Ok, read through all the comments above and tried all the formulas. I have a very similar situation and the formula I am copying from here isn't working ( sorry!). Help!

My scenario is if the 'Finish' is in 3 days and the '% complete' is not more than 70% or is blank, set the flag.

=IF(AND(Finish]@row >=TODAY(3), AND([% Complete]@row <>"")), 1, 0)

 

Mike Wilday

Hi Samiv

Try removing the end bracket after Finish ] 

That should do the trick for you. 

=IF(AND(Finish@row >=TODAY(3), AND([% Complete]@row <>"")), 1, 0)

You only need brackets if there is a space in your column name or it ends in a number. 

SamiV

Thank you Mike. I must be doing something wrong. It does not set the flag to red.

 

Paul Newcome

To have it set for is blank or less than 70%, you will want to include both criteria. You will also need to change <> to = for the blank portion. <> is the same as not equal to, so your current formula is saying if the day requirement is met and the % complete is not blank. You are also using an unnecessary AND function for the way you have it set.

.

Current Formula:

=IF(AND(Finish@row >=TODAY(3), AND([% Complete]@row <>"")), 1, 0)

.

Proper syntax for AND:

=IF(AND(Finish@row >=TODAY(3), [% Complete]@row <>""), 1, 0)

.

Correction for if it is blank:

=IF(AND(Finish@row >=TODAY(3), [% Complete]@row =""), 1, 0)

.

To accomplish both blank or less than 70%:

=IF(AND(Finish@row >=TODAY(3), OR([% Complete]@row ="", [% Complete]@row < .7)), 1, 0)

SamiV

Thank you Paul! The formula worked on one row and I was about to do the happy dance, then realized that this formula is not changing flags for the past due items. it is only turning flags Red if we are within 3 days. 

=IF(AND(Finish@row >=TODAY(3), OR([% Complete]@row ="", [% Complete]@row < .7)), 1, 0)

So, I added a condition to the formula to also look for anything that is past due and % complete is not 100% or blank as well, with this formula... and obviously I am not good with formulas.  Would you be able to help?

So the condition should be

Turn flag to red if we are within 3 days of due date and % complete is not more than .7 or if it is blank. Also turn the flag red with the due date is in the past and we are not at 100% or is blank.

 

 

Paul Newcome

So if Finish is in the past and % Complete is less than 100%,

or

if Finish is within the next 3 days and % Complete is less than 70%,

you want it flagged?

.

NOTE: Blank cells are considered to be 0%, so all we need to specify is that it is less than a particular percentage.

.

If the above criteria is correct, then you can use this...

 

=IF(OR(AND(Finish@row < TODAY(), [% Complete]@row < 1), AND(Finish@row <= TODAY(3), [% Complete]@row < .7)), 1)

 

SamiV

It works beautifully!!! Thank you so very much!

 I am off to my happy dance now!

Have a wonderful day Paul and Mike!

Mike Wilday

(－‸ლ)

I need to slow down. 

Mike Wilday

Thanks Sam! You too!