How to add new criteria to the current formula

Options

Hi

I have the formula below which is triggering the Manager Approval to Yes if threshold if meet. Right now i need to add another depended which is buyer status which is if cancel then the manager status will be Not Required. Need help on this

Current formula

=IF([xx Proposed Settlement ($USD)]@row >= {Buyer Supervisor Limit}, "YES", IF([xx Proposed Settlement ($USD)]@row = "", "TBD", "NO"))

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Options

    Hi @HZAR

    Please try the following formula:

    =IF([Buyer Status]@row = "cancel", "Not Required", IF([xx Proposed Settlement ($USD)]@row >= {Buyer Supervisor Limit}, "YES", IF([xx Proposed Settlement ($USD)]@row = "", "TBD", "NO")))
    

    This formula works as follows:

    1. It first checks if the Buyer Status for the current row is "cancel." If it is, the formula immediately returns "Not Required."
    2. If the Buyer Status is not "cancel," it then checks if the [xx Proposed Settlement ($USD)] value meets or exceeds the {Buyer Supervisor Limit}. If it does, it returns "YES."
    3. If the [xx Proposed Settlement ($USD)] value does not meet the threshold, it checks whether this value is empty (""). If it's empty, it returns "TBD."
    4. If none of the above conditions are met, it returns "NO."

    Make sure to replace [Buyer Status] with the actual column name in your Smartsheet where the buyer status is recorded. This formula assumes that the buyer status that triggers "Not Required" is specifically "cancel" and is case-sensitive. If there are variations in how "cancel" is recorded (like "Cancelled" or "canceled"), you'll need to adjust the condition to account for these variations.

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • HZAR
    HZAR ✭✭✭
    Options

    Hi Mr Bassam Khalil

    Thanks for the input. Really appreciate it 😍

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Options

    You are welcome @HZAR

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • HZAR
    HZAR ✭✭✭
    Options

    Hi Mr Bassam

    Is it possible for me to add 1 more criterial for example like below. If yes is then how can I add the criteria? Hope that you can guide me. Thanks

    =IF([Buyer Status]@row = "cancel", "Not Required", IF ([Buyer]@ row="CC2gmail,"cw@gmail","NO",IF([xx Proposed Settlement ($USD)]@row >= {Buyer Supervisor Limit}, "YES", IF([xx Proposed Settlement ($USD)]@row = "", "TBD", "NO")))
    

  • dojones
    dojones ✭✭✭✭
    Options

    Yes,

    IF([Buyer Status]@row = "cancel", "Not Required", IF ([Buyer]@ row="CC2gmail,"cw@gmail","NO",IF([xx Proposed Settlement ($USD)]@row >= {Buyer Supervisor Limit}, "YES", IF([xx Proposed Settlement ($USD)]@row = "", "TBD", "NO"
    

    You could add it at any point in the formula, but I'll describe it at the end of formula. Go to this point

     IF([xx Proposed Settlement ($USD)]@row = "", "TBD", "NO")))
      
    Add another IF statement at end after "TBD" and before "NO".  Add an extra parenthesis after "NO".
    
    IF([xx Proposed Settlement ($USD)]@row = "", "TBD", IF([xxx]@row = [yyy]@row,"Value to display if true", "NO"))))
    

  • HZAR
    HZAR ✭✭✭
    edited 05/13/24
    Options

    What if I want to change

    If the status is cancel -No

    If CC and CW for all status-No

    Can you help to advise since I have the formula below. Thanks

    =IF([GSP Status]@row = "Cancel", "NO", IF([MSI Proposed Settlement ($USD)]@row >= {Buyer Supervisor Limit}, "YES", IF([MSI Proposed Settlement ($USD)]@row = "", "TBD", IF(GSP@row = "cc@gmail", "cw@gmail", "NO", "NO"))))

  • HZAR
    HZAR ✭✭✭
    edited 05/13/24
    Options

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!