Formula missing 2 parenthesis
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/
Answers
-
@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
-
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"
-
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/
-
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/
-
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","-"
-
@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/
-
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","-"
-
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/
-
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")))
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 383 Global Discussions
- 212 Industry Talk
- 444 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 451 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 291 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!