IF Statement with several conditions not returning desired value

[JessMe]

Hi all, I'm trying to configure a cell to show status "complete" when all conditions are met, and "incomplete" when they're not, using nested IF-statements and an OR statement within that. The formula is not returning anything, i.e. the field stays blank. Any ideas why this could be?

I tried both these variations (and also only single conditions to see if it works at all, but the value returned isn't correct - I only got "incomplete", no matter whether the condition was met or not.)

Background: {Confirmed? (from Location)} and {Confirmed} are checkbox formats, {Status (from Menus)} is a single value field. I want the return value regardless of whether Status (from Menus) is "Done" or blank. Thanks so much for any hints!

IF(AND({Confirmed? (from Location)} = "CHECKED", {Confirmed} = "CHECKED", {Status (from Menus)} = OR("Done", "BLANK"()), "complete", "incomplete")) 

IF(AND({Confirmed? (from Location)} = "CHECKED", {Confirmed} = "CHECKED", {Status (from Menus)} = OR("Done", " "), "complete", "incomplete"))

[AirOps]

Hi @JessMe 

Welcome to the Airtable Community! We are happy you're here. 

Thank you for your detailed questions! Generally you are on the right track, there are just a couple of errors within both your formulas that are causing bad results. 

Here are both of your formulas fixed, both of them will provide the same results: 

IF(AND({Confirmed? (from Location)} = 1, {Confirmed} = 1, OR({Status (from Menus)} = "Done", {Status (from Menus)} = BLANK())), "complete", "incomplete")

IF(AND({Confirmed? (from Location)} = 1, {Confirmed} = 1, OR({Status (from Menus)} ="Done", {Status (from Menus)}= "")), "complete", "incomplete")

In terms of where your formulas were going wrong, here is a detailed explanation of the errors/what changed:

• The first mistake made was how Airtable formulas interpret a checked box. Airtable "Understands" a checked box to be either 1 or TRUE(), (on the flipside Airtable "Understands" an unchecked box to be 0 or FALSE()). "CHECKED" becomes 1 or TRUE()
• The second mistake was the use of the OR() function. When using OR() you need to use whole conditions for each argument, instead of using OR() for just the result. {Status (from Menus)} = OR("Done", "BLANK"()) becomes OR({Status (from Menus)} = "Done", {Status (from Menus)} = BLANK()). 
  • suberror here was "BLANK"() cannot be encased by quotations, it becomes: BLANK()
  • suberror here was in your second formula you used " " instead of BLANK, Airtable actually reads this as the character "space" rather than empty, if you want to check for empty you need to make sure this is no space between your quotations. " " becomes ""
• The final error was just a mix up with the brackets. The AND() closing bracket was placed at the end of the formula, not at the end of the AND() statement which is why you always saw blank results. 
  • Incorrect: IF(AND({Confirmed? (from Location)} = "CHECKED", {Confirmed} = "CHECKED", {Status (from Menus)} = OR("Done", "BLANK"()), "complete", "incomplete")) 
  • Correct: IF(AND({Confirmed? (from Location)} = 1, {Confirmed} = 1, OR({Status (from Menus)} = "Done", {Status (from Menus)} = BLANK())), "complete", "incomplete")

I hope this helps!