Need help writing a Nested IF, IF OR and IF AND Statements

I am trying to write the formula correctly, I have a couple different versions and neither are working.

I can get the first 2 parts to work in this version, but the last part fails giving me INCORRECT ARGUMENT

=IF(OR([Line of Business]@row = "Valuation", [Line of Business]@row = "Property Management"), "Final", IF(OR([Labor Allocation Category]@row = "PD - Producer", [Labor Allocation Category]@row = "AD - Management & Admin"), "Final", IF(AND([Labor Allocation Category]@row = "SB - Producer Support", [Contractual?]@row = "Yes", [% of Reimbursement (Requested Annual Comp)(Reques)]@row >= 0.5, "Final", "Conditional"))))


I received this version below and it is not working either. Its giving me ALL Conditional responses and that is not true.

=IF(AND(OR([Line of Business]@row = "Property Management", [Line of Business]@row = "Valuation"), OR([Labor Allocation Category]@row = "AD - Management & Admin", [Labor Allocation Category]@row = "PD - Producer", [Labor Allocation Category]@row = "SB - Producer Support"), [% of Reimbursement (Requested Annual Comp)(Reques)]@row >= 0.5, [Contractual?]@row = "Yes"), "Final", "Conditional")


Can anyone help me or tell me what is wrong with these formulas?

Tags:

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Looks like it may just be a misplaced parenthesis in the first one. Taking one from the end and using it to close out the AND function should get you what you need.


    =IF(OR([Line of Business]@row = "Valuation", [Line of Business]@row = "Property Management"), "Final", IF(OR([Labor Allocation Category]@row = "PD - Producer", [Labor Allocation Category]@row = "AD - Management & Admin"), "Final", IF(AND([Labor Allocation Category]@row = "SB - Producer Support", [Contractual?]@row = "Yes", [% of Reimbursement (Requested Annual Comp)(Reques)]@row >= 0.5), "Final", "Conditional")))


    It can also be slightly consolidated like so:

    =IF(OR([Line of Business]@row = "Valuation", [Line of Business]@row = "Property Management", [Labor Allocation Category]@row = "PD - Producer", [Labor Allocation Category]@row = "AD - Management & Admin", AND([Labor Allocation Category]@row = "SB - Producer Support", [Contractual?]@row = "Yes", [% of Reimbursement (Requested Annual Comp)(Reques)]@row >= 0.5)), "Final", "Conditional")

Answers

  • Danielle Arteaga
    Danielle Arteaga ✭✭✭✭✭✭

    Hi, Michelle -

    I tested the second formula you posted above on some sample data, and it did work for me:

    Hard to diagnose what might be going wrong without seeing your sheet, but I have two suspicions:

    1. Copy/paste error. Double check that you didn't inadvertently leave out any commas, brackets, or parenthesis. (As an aside, it's always a good idea to NOT include any types of non-alphanumeric characters in your column names if you can help it, because it can be REALLY hard to find mistakes in formulas later ...)
    2. Is your [Contractual?] column set up as a text column (so that you manually type in "Yes" or "No") or is it actually a checkbox column? If it IS a checkbox column, then the "Yes" in your formula should be changed to a 1 (no quotation marks).
  • Thanks for your feedback Danielle. The Contractual column is a dropdown. Do you notice anything else I could be doing wrong here?

    I want the formula to return "Final" if Line of Business = "Valuation" OR "Property Management",

    then I want it to return "Final" if Labor Allocation Category = "AD - Management & Admin" OR "PD - Producer",

    then I want it to return "Final if Labor Allocation Category= "SB - Producer Support") AND [% of Reimbursement (Requested Annual Comp)(Reques)]@row >= 0.5, AND [Contractual?]@row = "Yes"), OTHERWISE I want it to return "Conditional"

     

    Any help is appreciated. Thank you!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Looks like it may just be a misplaced parenthesis in the first one. Taking one from the end and using it to close out the AND function should get you what you need.


    =IF(OR([Line of Business]@row = "Valuation", [Line of Business]@row = "Property Management"), "Final", IF(OR([Labor Allocation Category]@row = "PD - Producer", [Labor Allocation Category]@row = "AD - Management & Admin"), "Final", IF(AND([Labor Allocation Category]@row = "SB - Producer Support", [Contractual?]@row = "Yes", [% of Reimbursement (Requested Annual Comp)(Reques)]@row >= 0.5), "Final", "Conditional")))


    It can also be slightly consolidated like so:

    =IF(OR([Line of Business]@row = "Valuation", [Line of Business]@row = "Property Management", [Labor Allocation Category]@row = "PD - Producer", [Labor Allocation Category]@row = "AD - Management & Admin", AND([Labor Allocation Category]@row = "SB - Producer Support", [Contractual?]@row = "Yes", [% of Reimbursement (Requested Annual Comp)(Reques)]@row >= 0.5)), "Final", "Conditional")

  • Paul I can't thank you enough for your help with this! I couldn't see that misplaced parenthesis :(. Also appreciate you sending a consolidated version. This works!! Thank you so much!!!

  • I will be using this Community much more often!!! Thanks again!!!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!