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?

Find more posts tagged with

Formulas

Accepted answers

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.

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

All comments

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:

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

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.

It can also be slightly consolidated like so:

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.