Trouble with long, nested formula...

corilowenstein

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?

Christian Graf

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.

Kelly Moore

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

Christian Graf

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.

corilowenstein

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

corilowenstein

@Christian Graf - thank you. Unfortunately it did not work.

Jeff Reisman

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

Christian Graf

@corilowenstein

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.

corilowenstein

@Christian Graf - thank you.

For some reason I'm still getting the UNPARSABLE error. Why do you think that is?

Christian Graf

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.