Help

Re: Can two unrelated conditions live in the same formula?

Solved
Jump to Solution
1088 3
cancel
Showing results for 
Search instead for 
Did you mean: 
908_Interiors
5 - Automation Enthusiast
5 - Automation Enthusiast

Dear community,

can two unrelated conditions live in the same formula? Do they need to connected by the same first argument?
image

I want "Sample Availability to read “Shop only” when there is no value in “SignOutIcon”


In writing it would be:

If SignOutIcon contains no value, return “Shop only”. If SignOutDate contains a value and ReturnDate contains no value, return “not in stock”, otherwise “available”


With first argument the same, it would be

If SignOutIcon contains no value, return “Shop only”. If SignOutIcon contains a value and SignOutDate contains a value and ReturnDate contains no value, return “not in stock”, otherwise “available”


I used “&” but this returned a mess:

IF(AND({SignOutDate},NOT({SignOutComment})),“not in stock”,“available”) & IF(NOT({SignOutIcon}), “Shop only”, “available”)

image


Could anyone give me a hint? Any help highly appreciated.

Best, Sisi

1 Solution

Accepted Solutions
Kamille_Parks
16 - Uranus
16 - Uranus

^that written as a formula would be:

IF(
   NOT({SignOutIcon}),
   "Shop only",
   IF(
      AND({SignOutIcon}, {SignOutDate}, NOT({ReturnDate})),
      "not in stock",
      "available"
   )
)

See Solution in Thread

7 Replies 7
Kamille_Parks
16 - Uranus
16 - Uranus

^that written as a formula would be:

IF(
   NOT({SignOutIcon}),
   "Shop only",
   IF(
      AND({SignOutIcon}, {SignOutDate}, NOT({ReturnDate})),
      "not in stock",
      "available"
   )
)

This is a great example of how to write a formula! First clarify the logic in English (or whatever your native language is), then translate it.

If SignOutIcon contains no value, return “Shop only”.

  • If translates into the IF function.
  • SignOutIcon contains no value translates into NOT({SignOutIcon})
  • return “Shop only” translates in to the second parameter of the IF function.

If SignOutIcon contains a value and SignOutDate contains a value and ReturnDate contains no value,

  • This If translates into a nested IF function.
  • The and translates into the AND function.
  • contains a value translates into the field name itself (except for numbers that might be zero)
  • return “not in stock”, otherwise “available” translate into the second and third parameters of the nested IF function.

The rest is dealing with pesky grammar of making sure that you have parenthesis and commas in the proper places.

Thank you Kuovonne, this will definitely help me the next time I am trying to write a formula. Much appreciated!

Dear Kamille,

thank you very much for your help. It all makes very much sense to me - only airtable replies that it is an invalid formula when I paste it in. I have already tried removing blanks and replacing quotation marks but without success. Any hints that could help me fix this would be highly appreciated.

Thank you!

There is a missing comma at the end of the second line.

Good catch. I edited my formula to reflect that.

Thank you both so much. Now it worked, fantastic!