Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Sep 11, 2024 12:38 PM
Im trying to write the same formula I have in excel in to Airtable and Airtable erases everything but the first if(and()) statement.
Solved! Go to Solution.
Sep 11, 2024 04:54 PM
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)
)
Sep 11, 2024 04:54 PM
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)
)
Sep 11, 2024 08:02 PM - edited Sep 11, 2024 08:09 PM
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))))
Sep 12, 2024 11:37 AM
Nice edit @Alexey_Gusev! Guard clauses are definitely the way to go, that's much simpler to read.
Sep 13, 2024 03:31 PM
This is exactly what I need, thank you so much! This was so very helpful!
Sep 13, 2024 03:32 PM
I didn't think of it this way, this is actually a really great idea, thank you!