# Formula Help (conditional statement)

Topic Labels: Formulas
Solved
1227 5
cancel
Showing results for
Did you mean:
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?

Heather

1 Solution

Accepted Solutions
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.

5 Replies 5
18 - Pluto

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!

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.

18 - Pluto

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:

18 - Pluto

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?

4 - Data Explorer

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

Heather