Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Formula Help (conditional statement)

Topic Labels: Formulas
Solved
Jump to Solution
1625 5
cancel
Showing results for 
Search instead for 
Did you mean: 
Heather_Manone
4 - Data Explorer
4 - Data Explorer

Hello and thank you in advance for taking a look at my question!

I would like to have a formula that analyzes two columns of data to return a specific amount, or 0.

Column 1: # Nights
Column 2: Cabin Name

If # Nights is greater than or equal to 7, AND Cabin Name is “299” then $1800, if not then $0
If # Nights is greater than or equal to 7, AND Cabin Name is “514” then $1850, if not then $0

I am struggling with the logic of whether the IF statement for cabin name comes first or the # of nights comes first. Also struggling with the IF(AND…) formula - or perhaps I should be using a different formula altogether?

Please help!
Heather

1 Solution

Accepted Solutions
Jason
Airtable Employee
Airtable Employee

Here’s an adjusted formula that will output 0 if the other conditions aren’t met:

IF(
   AND(
      {# Nights}>7,{Cabin Name}=299
   ),
   1800,
IF(
   AND(
      {# Nights}>7,{Cabin Name}=514
   ),
   1850,
   0
)
)

You need to add 0 as the ELSE part of the second conditional statement, and move the closing parenthesis for both statements after that.

See Solution in Thread

5 Replies 5

There are a few different ways that you could structure this formula, but here’s one way to do it:

IF(
AND({# nights}>7,{Cabin Name}=299),1800,
IF(
AND({# nights}>7,{Cabin Name}=514),1850)
)

I’m actually not sure how to get the formula to spit out the value 0 — Airtable removes the number 0 from the formula if you try to add 0 into the formula. So this formula will result in a blank field instead of 0.

Perhaps someone else could chime in on how to get it to spit out the number 0!

Jason
Airtable Employee
Airtable Employee

Here’s an adjusted formula that will output 0 if the other conditions aren’t met:

IF(
   AND(
      {# Nights}>7,{Cabin Name}=299
   ),
   1800,
IF(
   AND(
      {# Nights}>7,{Cabin Name}=514
   ),
   1850,
   0
)
)

You need to add 0 as the ELSE part of the second conditional statement, and move the closing parenthesis for both statements after that.

Oh, thanks, @Jason! Haha, that’s what I was trying to do, but now I realize that I must have typed my 0 within the wrong set of parentheses! I really need to start using a text editor app to do my formulas, instead of typing them directly into the formula field! :slightly_smiling_face:

Here are two other possible formulas with a slightly different approach.

These versions use a switch statement instead of nested IF statements, so they are a little more maintainable if you have many more cabins, or if you want to change up the number of nights.

These formulas assume that {Cabin Name} is a single select or single line text field. If it is a number field, just remove the quotes.

Option with nested SWITCH:

IF(Nights >= 7,
  SWITCH({Cabin Name},
    "299", 1800,
    "514", 1850,
    0
  ),
  0
)

Option with nested IF:

SWITCH({Cabin Name},
  "299", IF(Nights >= 7, 1800, 0),
  "514", IF(Nights >= 7, 1850, 0),
  0
)


If you have the answer to your question, please mark one of the posts as the solution. Otherwise, could you please give a bit more details and a screen capture?

Thank you very much. This was perfect, although I am still trying to figure out just HOW it works!

Heather