Skip to main content
Solved

Formula Help (conditional statement)

  • June 15, 2020
  • 5 replies
  • 52 views
H

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

Best answer by Jason11

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!


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

ScottWorld
Forum|alt.badge.img+35
  • ScottWorld
  • Genius
  • 9770 replies
  • June 15, 2020

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!

J
Forum|alt.badge.img+20
  • Jason11
  • Inspiring
  • 375 replies
  • Answer
  • June 15, 2020

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!


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.

ScottWorld
Forum|alt.badge.img+35
  • ScottWorld
  • Genius
  • 9770 replies
  • June 15, 2020

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:

kuovonne
Forum|alt.badge.img+29
  • kuovonne
  • Brainy
  • 6009 replies
  • June 15, 2020

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?

H
  • Heather_Manone
  • Author
  • New Participant
  • 1 reply
  • June 16, 2020

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.


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

Heather


Terms & ConditionsAccessibility statement