Trouble with long, nested formula...
Trying to have a column show status of requests, and have been having issues for days.
- IF [Provisional Requested Amount] is under 50000 and [COL Leader Approval] is Provisional Approval = Provisional Approval
- IF [Final Requested Amount] request is under 50000 and [COL Leader Approval] is Final Approval = Final Approval
- If [Final Requested Amount] or [Provisional Requested Amount] request is over 50000 and [COL Leader] is not blank [Leadership Approval] is Blank = Pending
- If [Provisional Requested Amount] is over 50000 and [COL Leader Approval] is Provisional Approval and [Leadership Approval] is Provisional Approval = Provisional Approval
- If [Final Requested Amount] request is over 50000 and [COL Leader Approval] is Final Approval and [Leadership Approval] is Final Approval = Final Approval
- If [COL Leader Approval] is Blank = Pending If [SLA Approval] is Approved with Audit or Approved without Audit = Pending
Can anyone help?
Best Answer
-
I missed an @row try this.
=IF(AND([Provisional Requested Amount]@row <50000, [COL Leader Approval]@row = "Provisional Approval"), "Provisional Approval", IF(AND([Final Requested Amount]@row <50000, [COL Leader Approval]@row = "Final Approval"), "Final Approval", IF(AND(OR([Final Requested Amount]@row >50000, [Provisional Requested Amount]@row >50000), ISBLANK([COL Leader]@row) = 0, ISBLANK([Leadership Approval]@row) = 1), "Pending", IF(AND([Provisional Requested Amount]@row <50000, [COL Leader Approval]@row ="Provisional Approval", [Leadership Approval]@row ="Provisional Approval"), "Provisional Approval", IF(AND([Final Requested Amount]@row <50000, [COL Leader Approval]@row ="Final Approval", [Leadership Approval]@row = "Final Approval"), "Final Approval", IF(ISBLANK([COL Leader Approval]@row) = 1, "Pending", IF(OR([SLA Approval]@row = "Approved with Audit", [SLA Approval]@row = "Approved without Audit"), "Pending")))))))
Also, is "COL Leader" also a column? I noticed you had that and "COL Leader Approval" in your pseudo code.
Answers
-
Hey @corilowenstein
Quick question for clarification. Can you show a screenshot of your [Provisional Requested Amount] column. Your request is asking for decisions based on requested amount but as written, "50k", this is a text string and text strings aren't numbers. I would like to see how the data in that column is actually being collected. This will determine if the data can be used directly, or if we need to manipulate it first. (In the screenshot make sure no sensitive data is being displayed - ie., customer names, etc)
Thanks,
Kelly
-
Hello @corilowenstein
Try this:
=IF(AND([Provisional Requested Amount]@row <50000, [COL Leader Approval] = "Provisional Approval"), "Provisional Approval", IF(AND([Final Requested Amount]@row <50000, [COL Leader Approval]@row = "Final Approval"), ""Final Approval", IF(AND(OR([Final Requested Amount]@row >50000 [Provisional Requested Amount]@row >50000), ISBLANK([COL Leader]@row) = 0, ISBLANK([Leadership Approval]@row) = 1), "Pending", IF(AND([Provisional Requested Amount]@row <50000, [COL Leader Approval]@row ="Provisional Approval", [Leadership Approval]@row ="Provisional Approval"), "Provisional Approval", IF(AND([Final Requested Amount]@row <50000, [COL Leader Approval]@row ="Final Approval", [Leadership Approval]@row = "Final Approval"), "Final Approval", IF(ISBLANK([COL Leader Approval]@row) = 1, "Pending", IF(OR([SLA Approval]@row = "Approved with Audit", [SLA Approval]@row = "Approved without Audit"), "Pending")))))))
Hope this helps.
-
@Kelly Moore Hi, thanks for your response.
It is 50000 not 50k. I will edit in my original response.
Attached is a screenshot of the column.
-
@Christian Graf - thank you. Unfortunately it did not work.
-
@corilowenstein You're in good hands with Kelly and Christian!
I just wanted to add some commentary on working with nested IFs. It's helpful to me to remind myself that no matter how many nested IFs I have, the formula always runs from left to right, and it stops when it gets to the first true logical statement. With that being true, when I build these nested IFs, I tend to start with my most open-ended criteria. For instance, say I'm populating a RYG symbol column based on numeric values in another column. With ranges of Green = < 35, Yellow for > 35 but less than 70, and Red for 70+, Red is my most open-ended criteria. So I might set my formula like this:
=IF(Number@row >= 70, "Red", IF(Number@row <= 35, "Green", "Yellow"))
In this example, if the Number is less than 70 (not red!) or greater than 35 (not green!), there's only one option left - Yellow. Had I used a different order, I might have gotten a different result:
=IF(Number@row <= 35, "Green", IF(Number@row > 35, "Yellow", IF(Number@row >= 70, "Red")))
With the above I would never get a "Red" result, because any number over 35 would trigger the true condition at Yellow.
So, for your long string of nested IFs, you'll need to evaluate your data options and follow each logic path, in order to make sure that you don't hit a true condition too soon that would prevent some of your conditions from ever being considered when they should be.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
My apologies, I had trouble writing the formula in the comment box here.
This one should work better:
=IF(AND([Provisional Requested Amount]@row <50000, [COL Leader Approval] = "Provisional Approval"), "Provisional Approval", IF(AND([Final Requested Amount]@row <50000, [COL Leader Approval]@row = "Final Approval"), "Final Approval", IF(AND(OR([Final Requested Amount]@row >50000, [Provisional Requested Amount]@row >50000), ISBLANK([COL Leader]@row) = 0, ISBLANK([Leadership Approval]@row) = 1), "Pending", IF(AND([Provisional Requested Amount]@row <50000, [COL Leader Approval]@row ="Provisional Approval", [Leadership Approval]@row ="Provisional Approval"), "Provisional Approval", IF(AND([Final Requested Amount]@row <50000, [COL Leader Approval]@row ="Final Approval", [Leadership Approval]@row = "Final Approval"), "Final Approval", IF(ISBLANK([COL Leader Approval]@row) = 1, "Pending", IF(OR([SLA Approval]@row = "Approved with Audit", [SLA Approval]@row = "Approved without Audit"), "Pending")))))))
If some of these statements don't give an answer, like Jeff said above, you will have to reorder them so the priority of the if statements lines up better.
-
@Christian Graf - thank you.
For some reason I'm still getting the UNPARSABLE error. Why do you think that is?
-
I missed an @row try this.
=IF(AND([Provisional Requested Amount]@row <50000, [COL Leader Approval]@row = "Provisional Approval"), "Provisional Approval", IF(AND([Final Requested Amount]@row <50000, [COL Leader Approval]@row = "Final Approval"), "Final Approval", IF(AND(OR([Final Requested Amount]@row >50000, [Provisional Requested Amount]@row >50000), ISBLANK([COL Leader]@row) = 0, ISBLANK([Leadership Approval]@row) = 1), "Pending", IF(AND([Provisional Requested Amount]@row <50000, [COL Leader Approval]@row ="Provisional Approval", [Leadership Approval]@row ="Provisional Approval"), "Provisional Approval", IF(AND([Final Requested Amount]@row <50000, [COL Leader Approval]@row ="Final Approval", [Leadership Approval]@row = "Final Approval"), "Final Approval", IF(ISBLANK([COL Leader Approval]@row) = 1, "Pending", IF(OR([SLA Approval]@row = "Approved with Audit", [SLA Approval]@row = "Approved without Audit"), "Pending")))))))
Also, is "COL Leader" also a column? I noticed you had that and "COL Leader Approval" in your pseudo code.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 439 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 67 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!