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

michelle.farsund-hall91806

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?

Paul Newcome

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")

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

michelle.farsund-hall91806

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

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")

michelle.farsund-hall91806

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!!!

Paul Newcome

Happy to help. 👍️

michelle.farsund-hall91806

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