Help

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

Multiple nested if statements giving me problems

Topic Labels: Formulas
Solved
Jump to Solution
408 5
cancel
Showing results for 
Search instead for 
Did you mean: 
KingTutankhamun
5 - Automation Enthusiast
5 - Automation Enthusiast

Im trying to write the same formula I have in excel in to Airtable and Airtable erases everything but the first if(and()) statement.

 

IF({Statement} = 1, "3",
     IF(AND({Statement2}<=50, {Statement2>0),1),
     IF(AND({Statement2}>50, {Statement2}<=100, 1.5)),
     IF(AND({Statement2}>100, 2)))
 
I don't get any errors, it just erases everything after that first if-and. Im sure it's something small that Im doing that's causing it not to work but I cannot seem to figure out exactly how to write this in a way that returns the values I need. 
This entire document is essentially going to be built like this because it relies on data from 3-4 columns.
 
Thanks for your help.
1 Solution

Accepted Solutions
Kenneth_Raghuna
7 - App Architect
7 - App Architect
IF(Statement = 1, "3",
     IF(
          AND(0 < Statement2, Statement2 <=50),
          1,
          IF(
               AND(50 < Statement2, Statement2 <= 100), 
               1.5,
               IF(
                    Statement2 > 100, 
                    2
               )
          )
     )
)

 

You just had the commas in the wrong spot. When you nest IF() statements, make sure to put the next condition to check in the value2 (false return) location.

IF(condition, value1, value2)

Note that the closing parenthesis is after value2.

Separating things by more lines can make things easier. Sometimes I prefer to write it out this way:

IF(
    Condition,
    value1(true return),
    value2(false return)
)

See Solution in Thread

5 Replies 5
Kenneth_Raghuna
7 - App Architect
7 - App Architect
IF(Statement = 1, "3",
     IF(
          AND(0 < Statement2, Statement2 <=50),
          1,
          IF(
               AND(50 < Statement2, Statement2 <= 100), 
               1.5,
               IF(
                    Statement2 > 100, 
                    2
               )
          )
     )
)

 

You just had the commas in the wrong spot. When you nest IF() statements, make sure to put the next condition to check in the value2 (false return) location.

IF(condition, value1, value2)

Note that the closing parenthesis is after value2.

Separating things by more lines can make things easier. Sometimes I prefer to write it out this way:

IF(
    Condition,
    value1(true return),
    value2(false return)
)
Alexey_Gusev
12 - Earth
12 - Earth

Hi,
it's not related to your error, but I would recommend to not mix strings and numbers in the possible output options. I mean "3" and 1 , 1.5 , 2, Unless you are doing it by purpose. Note that 1.5 can be shown as 2, if you not edit formatting and set number of decimals to 1. Alternatively, you can just use all strings for output, "1.5" will be always "1.5"

Also, to simplify the formula, think in the terms of 'guard clauses' : if St2 >100, it don't needs check for 0 and 50.

 

IF(Statement=1,3,
IF(Statement2>100,2,
IF(Statement2>50,1.5,
IF(Statement2>0,1))))

 




Kenneth_Raghuna
7 - App Architect
7 - App Architect

Nice edit @Alexey_Gusev! Guard clauses are definitely the way to go, that's much simpler to read.

This is exactly what I need, thank you so much! This was so very helpful!

I didn't think of it this way, this is actually a really great idea, thank you!