Skip to main content
Solved

Multiple nested if statements giving me problems

  • September 11, 2024
  • 5 replies
  • 45 views
K
Forum|alt.badge.img+4

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.

Best answer by Kenneth_Raghuna

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

5 replies

Kenneth_Raghuna
Forum|alt.badge.img+20
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) )
Justin_Barrett
Alexey_Gusev
Forum|alt.badge.img+25
  • Alexey_Gusev
  • Brainy
  • September 12, 2024

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
TheTimeSavingCo
Justin_Barrett
Kenneth_Raghuna
Forum|alt.badge.img+20

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

K
Forum|alt.badge.img+4
  • KingTutankhamun
  • Author
  • New Participant
  • September 13, 2024
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!

K
Forum|alt.badge.img+4
  • KingTutankhamun
  • Author
  • New Participant
  • September 13, 2024

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!

Terms & ConditionsAccessibility statement