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.
Best Answer
-
@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.
Answers
-
Try :
=IF(AND(Status4 <> "Complete", Status4 <> "Sent/Complete"), "not complete", "Complete")
-
Unfortunately, it still did not return the correct answer.
-
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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
-
@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 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.
-
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.
-
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 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!