Formula missing 2 parenthesis

Sherry Fox
Sherry Fox ✭✭✭✭✭✭
edited 03/13/24 in Formulas and Functions

It showed 2 parenthesis were missing. I attempted to add them to the end, however I got an error that the syntax was incorrect. Screenshot of the error shown at the bottom.

I am having issues with this formula. It is showing EVERYTHING as overdue (previous version). It is possible that the "Actual Implementation Complete Date" column could be before OR after the Target Implementation Date (which is a formula). Either way everything should not display as Overdue.


=IF(AND(status@row = "Complete", [Actual Implementation Complete Date]@row <= [Target Implementation Complete Date]@row), "No Escalation", IF(AND(status@row = "Complete", [Actual Implementation Complete Date]@row >= [Target Implementation Complete Date]@row), "Over Due", IF(AND(status@row = "Open", [Actual Implementation Complete Date]@row >= sum([Target Implementation Complete Date]@row - 14), [Actual Implementation Complete Date]@row <= [Target Implementation Complete Date]@row), "At Risk","-")))



Sherry Fox

Data Science & Reporting Specialist | PA Performance & Data Insights

UnitedHealth Group | OptumRx

EAP | Mobilizer | Automagician | Superstar | Community Champion

https://www.linkedin.com/in/sherryfox/

Tags:

Answers

  • Andrea Zenner
    Andrea Zenner ✭✭✭✭✭

    @Sherry Fox Not sure what is happening with your sheet. Your formula is correct. I recreated it and it works for me. The only difference is my status and target implementation dates are not calculated. Those calculated fields may be mucking with your formula, though then I would expect an error to be returned, not Over Due for everything.

    Sorry I can't be more help, but wanted you to know your formula is correct.



    Andrea Zenner

    Program Manager | Infrastructure & Operations

    Apogee Enterprises

    EAP | Mobilizer | Core Product Certified | Superstar

    azenner@apog.com

  • Nic Larsen
    Nic Larsen ✭✭✭✭✭✭

    Just a couple of observations - may or may not matter in grand scheme of what you're trying to achieve.

    1) Do you need the AND statement here in the first argument? If the row is Complete, do you still need to evaluate the date or can it just return "no escalation"? Would it ever show complete, but you still need to escalate?

    =IF(AND(status@row = "Complete", [Actual Implementation Complete Date]@row <= [Target Implementation Complete Date]@row), "No Escalation",

    2) Should this 2nd one be not equal to complete? Again, if its marked Complete, why look at the dates.

    IF(AND(status@row = "Complete", [Actual Implementation Complete Date]@row >= [Target Implementation Complete Date]@row), "Over Due"

  • Sherry Fox
    Sherry Fox ✭✭✭✭✭✭
    edited 03/13/24

    @Nic Larsen ,

    Thanks for responding. You are correct on BOTH counts (1 & 2). I attempted both formulas and got the same error for each (see screenshot below).

    Formula 1

    =IF(AND(Status@row = "Complete", [Actual Implementation Complete Date]@row <= [Target Implementation Date]@row, "No Escalation"))

    Formula 2

    =IF(AND(Status@row = "Complete", [Actual Implementation Complete Date]@row >= [Target Implementation Date]@row, "Over Due"))


    BOTH provided the same error. Did I do something wrong??


    Sherry Fox

    Data Science & Reporting Specialist | PA Performance & Data Insights

    UnitedHealth Group | OptumRx

    EAP | Mobilizer | Automagician | Superstar | Community Champion

    https://www.linkedin.com/in/sherryfox/

  • Sherry Fox
    Sherry Fox ✭✭✭✭✭✭

    @Andrea Zenner ,

    Thanks for your assistance. My data requires some formulas. This formula resides on what is referred as an Entity sheet, there are about 20+. About 10+ columns of data are entered into the Master file and then "feed" into the Entity sheets via formulas. This way they all update based on any changes made by the Program Manager. The team then makes their own updates to each sheet. I had never considered that there would be issues as a result of whether a cell contained a formula or raw data. In my eyes, it should not matter. Thanks again.

    Sherry Fox

    Data Science & Reporting Specialist | PA Performance & Data Insights

    UnitedHealth Group | OptumRx

    EAP | Mobilizer | Automagician | Superstar | Community Champion

    https://www.linkedin.com/in/sherryfox/

  • Nic Larsen
    Nic Larsen ✭✭✭✭✭✭

    The formulas I posted in my response were just copied from your original, I didn't make any changes. I just wanted to be sure it was clear what section I was talking about.

    I revised now and see if this works:

    =IF(status@row = "Complete", "No Escalation", IF(AND(status@row <> "Complete", [Actual Implementation Complete Date]@row >= [Target Implementation Complete Date]@row), "Over Due", IF(AND(status@row = "Open", [Actual Implementation Complete Date]@row >= sum([Target Implementation Complete Date]@row - 14), [Actual Implementation Complete Date]@row <= [Target Implementation Complete Date]@row), "At Risk","-"

  • Sherry Fox
    Sherry Fox ✭✭✭✭✭✭

    @Nic,

    I had to add 2 parenthesis again top this formula:

    =IF(status@row = "Complete", "No Escalation", IF(AND(status@row <> "Complete", [Actual Implementation Complete Date]@row >= [Target Implementation Complete Date]@row), "Over Due", IF(AND(status@row = "Open", [Actual Implementation Complete Date]@row >= sum([Target Implementation Complete Date]@row - 14), [Actual Implementation Complete Date]@row <= [Target Implementation Complete Date]@row), "At Risk","-")))


    Sherry Fox

    Data Science & Reporting Specialist | PA Performance & Data Insights

    UnitedHealth Group | OptumRx

    EAP | Mobilizer | Automagician | Superstar | Community Champion

    https://www.linkedin.com/in/sherryfox/

  • Nic Larsen
    Nic Larsen ✭✭✭✭✭✭

    It's these arguments that are causing your error. What are you trying to say here?

    IF(AND(status@row = "Open", [Actual Implementation Complete Date]@row >= sum([Target Implementation Complete Date]@row - 14), [Actual Implementation Complete Date]@row <= [Target Implementation Complete Date]@row), "At Risk","-"

  • Sherry Fox
    Sherry Fox ✭✭✭✭✭✭

    Nothing. Does this matter at all??

    Actual Implementation Complete Date - raw data cell

    Target Implementation Date - Formula

    Status - Formula

    Sherry Fox

    Data Science & Reporting Specialist | PA Performance & Data Insights

    UnitedHealth Group | OptumRx

    EAP | Mobilizer | Automagician | Superstar | Community Champion

    https://www.linkedin.com/in/sherryfox/

  • Nic Larsen
    Nic Larsen ✭✭✭✭✭✭

    Can try it this way. It didn't error out in testing, but not sure it's giving the At Risk result you're looking for as I don't quite follow the last IF statement.

    =IF(Status@row = "Complete", "No Escalation", IF(AND(Status@row <> "Complete", [Actual Implementation Complete Date]@row >= [Target Implementation Date]@row), "Over Due", IF(AND([Actual Implementation Complete Date]@row >= [Target Implementation Date]@row - 14, [Actual Implementation Complete Date]@row <= [Target Implementation Date]@row), "At Risk")))

  • Sherry Fox
    Sherry Fox ✭✭✭✭✭✭

    @Nic Larsen ,

    You are my hero!!!! That works perfectly! Thanks so much for all your help. I really appreciate this!!!!

    Sherry Fox

    Data Science & Reporting Specialist | PA Performance & Data Insights

    UnitedHealth Group | OptumRx

    EAP | Mobilizer | Automagician | Superstar | Community Champion

    https://www.linkedin.com/in/sherryfox/

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!