Skip to main content

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.
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)

)

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))))

 




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

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)

)

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

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))))

 





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

Reply


Terms & Conditions