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?
Best 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
-
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:
- 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 ...)
- 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!
-
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!!!
-
Happy to help. 👍️
-
I will be using this Community much more often!!! Thanks again!!!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!