OR function Not Working with Multiple Logical Expressions

Hello,

I have the following formula:

=IF(OR(Status4 <> "Complete", Status4 <> "Sent/Complete"), "not complete", "Complete")

which always returns "not complete", i.e. true.

However, modifying the expression so that only 1 logical expression is present, the expression works correctly, showing the true and false outcomes as expected. My question is, how do I modify this formula to work correctly and evaluate both logical expressions simultaneously.

Find more posts tagged with

Sheets

Formulas

Support

functionality

IT & Operations

Accepted answers

Paul Newcome

@Robert Gochee Yes. That is expected. That is why I asked @Fiona Painter for more detail as the provided solutions that we have all thought of are not giving the desired results.

All comments

Eid E. Eid

Try :

=IF(AND(Status4 <> "Complete", Status4 <> "Sent/Complete"), "not complete", "Complete")

Fiona Painter

Unfortunately, it still did not return the correct answer.

Andrée Starå

@Fiona Painter

Hi Fiona,

I'm guessing that you don't want to show a status when the cell is empty.

Try something like this.

=IF(ISBLANK(Status@row); ""; IF(OR(Status@row <> "Complete"; Status@row <> "Sent/Complete"); "not complete"; "Complete"))

The same version but with the below changes for convenience.

=IF(ISBLANK(Status@row), "", IF(OR(Status@row <> "Complete", Status@row <> "Sent/Complete"), "not complete", "Complete"))

Depending on your country/region, you'll need to exchange the comma to a period and the semi-colon to a comma.

Would that work?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Robert Gochee

Fiona,

This should work for you - Anything other in the Status Column other than Complete or Sent/Complete will return Not Complete.

=IF(NOT([Status]@row = "Complete"), IF(NOT([Status]@row = "Sent/Complete"), "Not Complete", "Complete"), "Complete")

Rob

Paul Newcome

@Robert Gochee Your solution is actually a different version of @Eid E. Eid's solution and will have the same outputs.

@Fiona Painter Are you able to provide more details? Can you write out what you expect to happen such as

"If the Status is not "Complete" or not "Sent/Complete" then the output should be "not complete", otherwise it should be "Complete"."

What are the other options for the Status column? If those two options are the only two containing the word "Complete" then we could use something like this...

=IF(CONTAINS("Complete", Status@row), "Complete", "not complete")

My initial thought based on your post though would be the AND should have worked.

Robert Gochee

@Paul Newcome,

All 3 of these suggestions lead to the same results

Paul Newcome

@Robert Gochee Yes. That is expected. That is why I asked @Fiona Painter for more detail as the provided solutions that we have all thought of are not giving the desired results.

Fiona Painter

Thanks for the tips everyone! I ended up adding some extra columns and breaking the formula down in each of those before parsing it together and got the answer that I was looking for.

Andrée Starå

@Fiona Painter

Excellent! Glad you got it working!

I'm always happy to help!

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.