At Risk Flag & IF Function

brandi.meyer80376brandi.meyer80376 ✭✭✭✭✭
edited 12/09/19 in Using Smartsheet
02/28/19 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)

Previous13

Comments

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Very close

     

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

  • Mike WildayMike Wilday ✭✭✭✭✭

    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. 

  • brandi.meyer80376brandi.meyer80376 ✭✭✭✭✭

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

  • 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)

     

  • Mike WildayMike Wilday ✭✭✭✭✭

    Make sure the column names are spelled exactly! 

  • brandi.meyer80376brandi.meyer80376 ✭✭✭✭✭

    =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. 

  • brandi.meyer80376brandi.meyer80376 ✭✭✭✭✭

    I did double check that multiple times! LOL 

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

  • brandi.meyer80376brandi.meyer80376 ✭✭✭✭✭

    I tried this and still got the #Unparseable alert. :(

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    You are misplacing the 2 (and I misread your original post as AND instead of OR)

     

    =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)

  • Mike WildayMike Wilday ✭✭✭✭✭

    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)

  • brandi.meyer80376brandi.meyer80376 ✭✭✭✭✭

    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. 

  • Mike WildayMike Wilday ✭✭✭✭✭

    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)

  • 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!!

     

     

  • Paul NewcomePaul Newcome ✭✭✭✭✭

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

     

    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.

  • Mike WildayMike Wilday ✭✭✭✭✭

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

Sign In or Register to comment.