Can someone help me put together a complex logic formula?

I'm looking to put together a formula that outputs a status based on a few criteria.

This formula should output "Denied" by default.

If the [Price] is $500 or less and [ManagerApproval] = "Approved", output "Confirmed".

If the [Price] is more than $500 but less than $1000 and [ManagerApproval] = "Approved and [DirectorApproval] = "Approved", output "Confirmed".

If the [Price] is more than $1000 and [ManagerApproval], [DirectorApproval], and [ChiefOfficerApproval] all = "Approved", output "Confirmed"

I'm going to keep working on this myself, but if anyone has a decent implementation idea for this, please let me know.


Thank you so much!

Best Answer

  • JamesB
    JamesB ✭✭✭✭✭✭
    Answer ✓

    @danielccascadecomp

    This should work.

    =IF(AND(Price@row < 500, ManagerApproval@row = "Approved"), "Confirmed", IF(AND(Price@row < 1000, ManagerApproval@row = "Approved", DirectorApproval@row = "Approved"), "Confirmed", IF(AND(Price@row > 1000, ManagerApproval@row = "Approved", DirectorApproval@row = "Approved", ChiefOfficerApproval@row = "Approved"), "Confirmed", "Unconfirmed")))

Answers

  • JamesB
    JamesB ✭✭✭✭✭✭
    Answer ✓

    @danielccascadecomp

    This should work.

    =IF(AND(Price@row < 500, ManagerApproval@row = "Approved"), "Confirmed", IF(AND(Price@row < 1000, ManagerApproval@row = "Approved", DirectorApproval@row = "Approved"), "Confirmed", IF(AND(Price@row > 1000, ManagerApproval@row = "Approved", DirectorApproval@row = "Approved", ChiefOfficerApproval@row = "Approved"), "Confirmed", "Unconfirmed")))

  • Leibel S
    Leibel S ✭✭✭✭✭✭

    This should work:

    =IF(Price@row <= 500, IF(ManagerApproval@row = "Approved", "Confirmed", "Denied"), IF(Price@row <= 1000, IF(AND(ManagerApproval@row = "Approved", DirectorApproval@row = "Approved"), "Confirmed", "Denied"), IF(AND(ManagerApproval@row = "Approved", DirectorApproval@row = "Approved", ChiefOfficerApproval@row = "Approved"), "Confirmed", "Denied")))

  • Eric_
    Eric_ ✭✭✭
    edited 02/20/24

    You already wrote your formula with your detailed explanation 😀. You can nest IF statements and use the AND operator.

    =IF(AND(Price@row <= 500, ManagerApproval@row = "Approved"), "Confirmed", IF(AND(Price@row < 1000, ManagerApproval@row = "Approved", DirectorApproval@row = "Approved"), "Confirmed", IF(AND(ManagerApproval@row = "Approved", DirectorApproval@row = "Approved", ChiefOfficerApproval@row = "Approved"), "Confirmed", "Denied")))

  • Linda Manduchova
    Linda Manduchova ✭✭✭✭

    Hello @danielccascadecomp -

    Try out the following formula:

    =IF(OR(AND(Price@row <= 500, ManagerApproval@row = "Approved"), AND(Price@row > 500, Price@row < 1000, ManagerApproval@row = "Approved", DirectorApproval@row = "Approved"), AND(Price@row >= 1000, ManagerApproval@row = "Approved", DirectorApproval@row = "Approved", ChiefOfficerApproval@row = "Approved")), "Confirmed", "Denied")


    Cheers,

    Linda

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!