# At Risk Flag & IF Function

Options
edited 12/09/19

I am trying to set an IF function to the At Risk column.  I would like it to flag if the end date is within 2days or if the % complete task is less than 80%.  Can someone help me set that function up?

This is what I am putting in;

=IF(AND([End Date]3 2>=TODAY() + 1, [% Complete]3 <.8) 1, 0)

«13

• ✭✭✭✭✭✭
Options

Very close

=IF(AND([End Date]3 >=TODAY(2), [% Complete]3 <.8) 1, 0)

• ✭✭✭✭✭✭
Options

IF you want it to be Either one OR the other... then instead of AND you'd use OR.

=IF(OR([End Date]@row >=TODAY(2), [% Complete]@row <.8) 1, 0)

I also switched it up to not depend on Absolute references it will look at any row its placed int.

• Options

I am still getting an #Unparseable notification. Any other thoughts

• ✭✭✭✭✭✭
Options

Can you copy and paste your exact syntax.  (Unparseable usually means a comma in the wrong place or a bracket missing or something like that)

• ✭✭✭✭✭✭
Options

Make sure the column names are spelled exactly!

• Options

=IF(AND([End Date]3 2>=TODAY() + 1, [% Complete]3 <.8) 1, 0)

This is exactly how I have it typed in the cell. I have tried it with the OR function too.

• Options

I did double check that multiple times! LOL

I know we can all be silly and miss simple things.

• Options

I tried this and still got the #Unparseable alert.

• ✭✭✭✭✭✭
Options

=IF(OR([End Date]@row >=TODAY(2), [% Complete]@row <.8) 1, 0)

Try typing this exactly. (Thanks for catching that AND/OR mix-up, Mike)

• ✭✭✭✭✭✭
Options

Hi Brandi, see the bold text in your formula below to identify the issue. That bold 2 is messing up your formula. Try copying and pasting Mine or Paul's and see if that works.

=IF(AND([End Date]3 2>=TODAY() + 1, [% Complete]3 <.8) 1, 0)

• Options

I copied/pasted both your examples. I really dont know why this isn't working. The formula is perfect! You are both amazing. I wish I could share screens to show you excatly what I am looking at.

• ✭✭✭✭✭✭
Options

I SEE IT!

There is a missing comma in Paul's original formula that I copied to change it to the OR statement. Try this one:

=IF(OR([End Date]@row >=TODAY(2), [% Complete]@row <.8), 1, 0)

• Options

Hey there!

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

I also tested this out and found out that if the % Complete column is empty, the flag is still red regardless of what date is entered (since "blank" is considered less than 80%, it satisfies the OR requirement)

I'm not sure how you intend this to function, so here's what I came up with (ignoring the flag if the % column is empty):

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

and here's the option if you prefer for it to still show the flag when the column is empty:

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

Also, is the formula supposed to trigger the flag if the end date is within 2 days of the deadline or "end" date?

If so, would the correct symbol be <= rather than >=, indicating the deadline is approaching the 2-day warning period, instead of beyond 2 days out?

Hope this helps!!

• ✭✭✭✭✭✭
Options

That's it, Mike. Just pass the buck on to me for missing that comma. That's what you get for copy/pasting...

Good catch though. I don't know how I missed that. We'll go with it's Friday eve, and I just can't brain today.

• ✭✭✭✭✭✭
Options

HAHA. Sorry. It's something I've done before and missed frequently.  Wasn't trying to throw you under the bus or anything. So Sorry!